SQL Server 导入Xml

--1.需要建立本地SQL实例
--2.权限OPENROWSET 权限由传递给 OLE DB 访问接口的用户名的权限确定。
--若要使用 BULK 选项，则需要有 ADMINISTER BULK OPERATIONS 权限。
--添加固定服务器角色：BULK INSERT ADMINISTRATOR


----导入本机路径下的XML文件----------------------------------------------------------------------------------------
----查看XML字符串
use XMLTest
declare @xmlText xml
set @xmlText = (select doc from openrowset(bulk 'D:\FordAS\ODP System Test\0测试Data\OBResult.xml',single_blob) as t(doc))  
select @xmlText
---------------------------------------------------------------------------
----将XML字符串读入数据库表
use XMLTest
drop table TempT1
CREATE TABLE TempT1(XmlCol xml);
GO
select * from TempT1
INSERT TempT1
SELECT CONVERT(xml,BulkColumn, 2) FROM OPENROWSET(Bulk 'D:\FordAS\ODP System Test\0测试Data\ImportSent.xml',SINGLE_BLOB) [rowsetresults]
SELECT * FROM TempT1 t
------------------------------------------------------------


----将读入数据库表的XML字符串分列显示
DECLARE @idoc int 
DECLARE @doc xml--varchar(max) 
--sample XML document 
SET @doc =(SELECT XmlCol   FROM TempT1 t)
select @doc
-- Create an internal representation of the XML document. 
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 


-- Execute a SELECT statement using OPENXML rowset provider.


----------header-------------
drop table #Temp1
SELECT * 
into #Temp1
FROM OPENXML (@idoc, '/data/beans/header', 2) 
      WITH (
batchNo varchar(200),
createdDate varchar(200),
dataCount varchar(200)
)
 
drop table Temp_header
SELECT * into Temp_header FROM #Temp1




---------------------------------beans---------
drop table #Temp2
SELECT * 
into #Temp2
FROM OPENXML (@idoc, '/data/beans/bean', 2) 
      WITH (
--batchNo varchar(200),
--createdDate varchar(200),
--dataCount varchar(200),
yearMonth varchar(200),
odpType varchar(200),
salesDate varchar(200),
batchNo varchar(200),
tvbCreateDate varchar(200),
dealerCode varchar(200),
dealerName varchar(200),
ownerId varchar(200),
vin varchar(200),
color varchar(200),
series varchar(200),
model varchar(200),
catCode varchar(200),
licenseNo varchar(200),
ownerName varchar(200),
gender varchar(200),
certificateType varchar(200),
certificateCode varchar(200),
birthday varchar(200),
marriage varchar(200),
phone varchar(200),
mobile varchar(200),
province varchar(200),
city varchar(200),
district varchar(200),
address varchar(200),
zipCode varchar(200),
email varchar(200),
contactName varchar(200),
contactGender varchar(200),
contactPhone varchar(200),
contactMobile varchar(200),
contactEmail varchar(200),
contactAddress varchar(200),
carSync varchar(200),
carEa varchar(200),
fordPass varchar(200),
subCcNumber varchar(200),
obType varchar(200),
priority varchar(200),
dupType varchar(200),
remark varchar(200),
dpId varchar(200),
sendDate varchar(200),
rebound varchar(200),
reboundReason varchar(200),
If_OBNavigation varchar(200)
  ) 


alter table #Temp2 add RID int IDENTITY(1,1) NOT NULL
-------根据需要选取列名放入临时表
drop table Temp_Beans
SELECT * into Temp_Beans FROM #Temp2




 select * from Temp_header
 select * from Temp_Beans 
 EXEC sp_xml_removedocument @idoc