CODEWITHSUNDEEP
t-sqlsql-server-2012
Jayesh Prakash
Jayesh Prakash

Reputation: 141

OPENXML Leaves out xml rows that do not have the specified elements

I am running below query on SQL Server 2014 server but I can't understand why I am getting only one row in result.

DECLARE @idoc int, @doc varchar(1000);   

SET @doc ='  
<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10">
                    <ReturnDetail ReturnOrderID="1111" ReturnDate="1996-08-04T00:00:00"/>
            </OrderDetail>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';  

--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   

-- SELECT stmt using OPENXML rowset provider  
SELECT *  
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail/ReturnDetail',2)   
         WITH ( OrderID       int         '../@OrderID',   
                order_CustomerID  varchar(10) '../../@CustomerID',   
                EmployeeID  varchar(10) '../../@EmployeeID',
                ContactName  varchar(100) '../../../@ContactName',
                CustomerID  varchar(10) '../../@CustomerID',
                OrderDate   datetime    '../../@OrderDate',   
                ProdID      int         '../@ProductID',   
                Qty         int         '../@Quantity',
                ReturnOrderID int '@ReturnOrderID',
                ReturnDate datetime '@ReturnDate'
                );

Output by above query

How do I get all 3 records returned like below

enter image description here

Upvotes: 0

Views: 43

Answers (1)

marc_s
marc_s

Reputation: 754488

You should use the built-in, native XQuery support (instead of the legacy OPENXML stuff...).

Use this code to get all the details down to the <OrderDetail> node (define your @doc variable as XML):

DECLARE @doc XML;

SET @doc = '......';

SELECT
    OrderID = XOD.value('@OrderID', 'int'),
    CustomerID = XCus.value('@CustomerID', 'varchar(20)'),
    ContactName = XCus.value('@ContactName', 'varchar(50)'),
    EmployeeID = XOrder.value('@EmployeeID', 'int'),
    OrderDate = XOrder.value('@OrderDate', 'datetime'),
    ProductID = XOD.value('@ProductID', 'int'),
    Quantity = XOD.value('@Quantity', 'int'),
    ReturnOrderID = RetD.value('@ReturnOrderID', 'int'),
    ReturnDate = RetD.value('@ReturnDate', 'datetime')
FROM
    @doc.nodes('/ROOT/Customer') AS XT(XCus)
CROSS APPLY
    XCus.nodes('Order') AS XT2(XOrder)
CROSS APPLY
    XOrder.nodes('OrderDetail') AS XT3(XOD)
OUTER APPLY
    XOD.nodes('ReturnDetail') AS XT4(RetD)

For each "level" that might contain multiple nodes (like <Customer> under <ROOT> etc.), you need to use CROSS APPLY and the .nodes() XQuery function to get all the child nodes - not just one (first or arbitrary).

Upvotes: 3

Related Questions