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

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


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


Following is the resultset of the above query using XQUERY

XQUERY Result set