sql - XML 到 SQL - SQL Server

Question

我正在遍历 XML 文件以读取节点并填充到 SQL Server 表中。我有一个包含 Department 节点的 Root 节点，该节点可能还有一个或多个作为元素。我想从 SQL 结果集中选择所有可能的值。

请在下面找到我指的 XML:

DECLARE @x XML='
<Root>
<Department>
<DeptID>D101</DeptID>
<DeptID>D102</DeptID>
</Department>
</Root>'

我正在使用下面的 SQL 查询从 XML 中获取数据，但是当我在 DeptID[1] 中传递 [1] 时，我只能读取第一个 DeptID。如果我通过 [2]，我可以获得第二个值。但在现实生活中，我无法知道 XML 中会有多少 DeptID。所以我想要一个通用脚本来读取 XML 中的 DeptID 数量。

SELECT  n.value('DeptID[1]','varchar(10)') AS DeptID FROM @x.nodes('/Root/Department') R(n)

Answer 1

您可以使用sql server 的OpenXMl 方法获取表中的更多元素，如下所示。

第 1 步:假设这是您的示例 XML 数据。

DECLARE @XML XML='
<ROOT>
<Customers>
<Customer CustomerID="C001" CustomerName="Arshad Ali">
<Orders>
<Order OrderID="10248" OrderDate="2012-07-04T00:00:00">
<OrderDetail ProductID="10" Quantity="5" />
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 1, 2, 3</Address>
</Customer>
<Customer CustomerID="C002" CustomerName="Paul Henriot">
<Orders>
<Order OrderID="10245" OrderDate="2011-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12" />
<OrderDetail ProductID="42" Quantity="10" />
</Order>
</Orders>
<Address> Address line 5, 6, 7</Address>
</Customer>
<Customer CustomerID="C003" CustomerName="Carlos Gonzlez">
<Orders>
<Order OrderID="10283" OrderDate="2012-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3" />
</Order>
</Orders>
<Address> Address line 1, 4, 5</Address>
</Customer>
</Customers>
</ROOT>'

第 2 步:使用 OPENXML 方法获取任意级别的元素，如下所示。

DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)

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

以上步骤将为您提供以下输出。