SQL – How to Parse XML Using T-SQL

Sometimes you may have a need to use XML in SSMS. To parse XML using Transact-SQL (T-SQL), you can use XQuery (XML Query) methods and expressions. The methods used in this example are value() and nodes().

The value() method returns a scalar value of SQL type. The syntax is value(XQuery, SQLType). Check here for more info.

The nodes() method shreds an XML into relational data and helps identify nodes that maps into a new row. The syntax is nodes(XQuery) AS Table(Column). Check here for more info.

Let’s dive into this:

Quick notes:

• Everything in single quotes (except the SQL data types) are considered XQuery expressions
  • Have to use ‘*:’ to tell it is a field/element
  • Forward slash ‘/’ is considered a step in
  • The element ‘[1]’ or ‘[2]’ is added to explicitly indicate the path expression to return a singleton
• Use the FROM statement to get to the element level you want such as People or Person
  • Must be aliased – I like to use the column as the element name I’m in
• Use the SELECT statement to get the value
• Use CROSS APPLY is if you need multiple elements in the same SELECT statment