我想将XML数据分解为sql server中的表格格式。

DECLARE @x AS XML;
SET @x = N'
<CustomersOrders>
    <Customer custid="1">
      <companyname>Customer NRZBB</companyname>
      <Order orderid="10692">
        <orderdate>2007-10-03T00:00:00</orderdate>
      </Order>
      <Order orderid="10702">
        <orderdate>2007-10-13T00:00:00</orderdate>
      </Order>
      <Order orderid="10952">
        <orderdate>2008-03-16T00:00:00</orderdate>
      </Order>
    </Customer>
    <Customer custid="2">
      <companyname>Customer MLTDN</companyname>
      <Order orderid="10308">
        <orderdate>2006-09-18T00:00:00</orderdate>
      </Order>
      <Order orderid="10926">
        <orderdate>2008-03-04T00:00:00</orderdate>
      </Order>
    </Customer>
</CustomersOrders>';

SELECT
    T.c.value('./@custid','INT') AS custid,
    T.c.value('./companyname','NVARCHAR(30)') AS companyname,
    T.C.value('../@orderid','INT') AS orderid
FROM  @x.nodes('//Customer') T(c)

I am trying to shred xml data to table, but I get an error:

我试图将xml数据分解到表中，但是我得到了一个错误:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

XQuery [value()]: 'value()'需要一个单例(或空序列)，找到了类型'xdt:untypedAtomic *'的操作数

The result should be similar to the result here:

结果应该与这里的结果相似:

custid  companyname     orderid  orderdate
1       Customer NRZBB  10692    2007-10-03 00:00:00.000
1       Customer NRZBB  10702    2007-10-13 00:00:00.000
1       Customer NRZBB  10952    2008-03-16 00:00:00.000
2       Customer MLTDN  10308    2006-09-18 00:00:00.000
2       Customer MLTDN  10926    2008-03-04 00:00:00.000

2 个解决方案

#1

You need to return only 1 element, like:

您只需要返回一个元素，例如:

SELECT
    T.c.value('../@custid[1]','INT') AS custid,
    T.c.value('../companyname[1]','NVARCHAR(30)') AS companyname,
    T.c.value('./@orderid[1]','INT') AS orderid,
    T.c.value('./orderdate[1]','datetime') AS orderdate
FROM  @x.nodes('//Customer/Order') T(c)

#2

You need to use two nested calls to .nodes() to handle the 1:n customers and each with 1:n orders:

您需要使用两个嵌套调用.node()来处理1:n个客户，每一个都有1:n个订单:

SELECT
    CustId = XC.value('@custid', 'INT') ,
    CompanyName = XC.value('(companyname)[1]', 'NVARCHAR(30)'),
    OrderID = XO.value('@orderid', 'INT'),
    OrderDate = XO.value('(orderdate)[1]', 'DATETIME2(3)')
FROM  
    @x.nodes('/CustomersOrders/Customer') AS XT(XC)
CROSS APPLY
    XC.nodes('Order') AS XT2(XO)

#1