I want to shred XML data to tabular format in sql server

1
votes
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:

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type '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
sqlsql-serverxmlsql-server-2012xquery

2 Answers

3
votes

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)
1
votes

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

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)