Parsing XML data in SQL Server
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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
