Parsing XML data in SQL Server

November 21, 2018 (Last Modified: November 15, 2018)

Fadıl

To support multi-value params, many times we use XML data type as input param.

There are multiple ways to parse XML data in SQL Server. Lets have a look at them

We will use the following XML data

<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
</ShoppingCart>

OPENXML
To parse the XML using OPENXML we use the following code

DECLARE @x XML =
'<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
</ShoppingCart>
'
DECLARE @iDoc INT
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @x

SELECT  *
FROM    OPENXML(@iDoc,'/ShoppingCart/Purchase')
WITH    (
            ProductID   INT
            ,Price      MONEY
            ,SaleDate   SMALLDATETIME
            ,SaleBatchID INT
            ,CustomerID INT
        )

EXECUTE sp_xml_removedocument @iDoc

Following is the resultset of the above query using OPENXML

XQUERY

With SQL SERVER 2005 onwards, since XML datatype is supported in the Database Engine itself,
we can use XQUERY as Follows

DECLARE @x XML =
'<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523" 
CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523"
 CustomerID = "2398"/>
</ShoppingCart>
'
SELECT  ShoppingCart.col.value('@ProductID','INT') ProductID
        ,ShoppingCart.col.value('@Price','MONEY') Price
        ,ShoppingCart.col.value('@SaleDate','SMALLDATETIME') SaleDate
        ,ShoppingCart.col.value('@SaleBatchID','INT') SaleBatchID
        ,ShoppingCart.col.value('@CustomerID','INT') CustomerID
FROM    @x.nodes('//ShoppingCart/Purchase') AS ShoppingCart(col)

Following is the resultset of the above query using XQUERY