将数据从XML⽂件导⼊和处理到SQLServer表中
使⽤OPENROWSET从XML⽂件导⼊XML数据
我有⼀个从FTP位置下载到本地⽂件夹的XML⽂件，该XML⽂件中的数据如下所⽰。
现在，为了将数据从XML⽂件导⼊到SQL Server中的表中，我正在使⽤OPENROWSET函数，如下所⽰。
在下⾯的脚本中，我⾸先创建⼀个带有⼀列数据类型为XML的表，然后使⽤OPENROWSET函数通过指定XML⽂件的⽂件位置和名称从⽂件中读取XML数据，如下所⽰：
CREATE DATABASE OPENXMLTesting
GO
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\l', SINGLE_BLOB) AS x;
SELECT * FROM XMLwithOpenXML
查询导⼊了XML数据的表时，它看起来像这样。XMLData列是XML数据类型，它将输出超链接，如下所⽰：
单击上图中的超链接，将在SSMS中打开另⼀个选项卡，其中显⽰XML数据，如下所⽰。
<ROOT>
<Customers>
<Customer CustomerName="Arshad Ali" CustomerID="C001">
<Orders>
<Order OrderDate="2012-07-04T00:00:00" OrderID="10248">
<OrderDetail Quantity="5" ProductID="10" />
<OrderDetail Quantity="12" ProductID="11" />
<OrderDetail Quantity="10" ProductID="42" />
</Order>
</Orders>
<Address> Address line 1, 2, 3</Address>
</Customer>
<Customer CustomerName="Paul Henriot" CustomerID="C002">
<Orders>
<Order OrderDate="2011-07-04T00:00:00" OrderID="10245">
<OrderDetail Quantity="12" ProductID="11" />
<OrderDetail Quantity="10" ProductID="42" />
</Order>
</Orders>
<Address> Address line 5, 6, 7</Address>
</Customer>xml文件怎么打开
<Customer CustomerName="Carlos Gonzlez" CustomerID="C003">
<Orders>
<Order OrderDate="2012-08-16T00:00:00" OrderID="10283">
<OrderDetail Quantity="3" ProductID="72" />
</Order>
</Orders>
<Address> Address line 1, 4, 5</Address>
</Customer>
</Customers>
</ROOT>
使⽤OPENXML函数处理XML数据
USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer')
WITH
(
CustomerID [varchar](50) '@CustomerID',
CustomerName [varchar](100) '@CustomerName',
Address [varchar](100) 'Address'
)
EXEC sp_xml_removedocument @hDoc
GO
如果要导航回⽗级或祖级⽗级并从那⾥获取数据，则需要使⽤“ ../”读取⽗级数据，并使⽤“ ../../”读取祖级⽗级数据USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order')
WITH
(
CustomerID [varchar](50) '../../@CustomerID',
CustomerName [varchar](100) '../../@CustomerName',
Address [varchar](100) '../../Address',
OrderID [varchar](1000) '@OrderID',
OrderDate datetime '@OrderDate'
)
EXEC sp_xml_removedocument @hDoc
GO
USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT CustomerID, CustomerName, Address, OrderID, OrderDate, ProductID, Quantity
FROM OPENXML(@hDoc, 'ROOT/Customers/Customer/Orders/Order/OrderDetail')
WITH
(
CustomerID [varchar](50) '../../../@CustomerID',
CustomerName [varchar](100) '../../../@CustomerName',
Address [varchar](100) '../../../Address',
OrderID [varchar](1000) '../@OrderID',
OrderDate datetime '../@OrderDate',
ProductID [varchar](50) '@ProductID',
Quantity int'@Quantity'
)
EXEC sp_xml_removedocument @hDoc
GO