Extracting data from XML

Getting data from XML is much easier with native support of XML in SQL Server. But it still requires solid knowledge of XML specific things like XPATH or XQUERY. The goal of this article is to collect some useful examples of how to extract values from XML and save time when searching for it. Most of them are easy to understand and simple for copy and edit purposes.

1. Extracting values using .nodes method and XPATH navigation

Sample XML:

DECLARE @XML XML

SET @XML = 
N'
<Rows xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Description = "Test Description" User = "test_user">
   <Row RowId = "1">
      <Columns>
         <Column Name = "Col1" Value = "50"/>
      </Columns>
   </Row>
   <Row RowId = "2">
      <Columns>
         <Column Name = "Col1" Value = "70"/>
         <Column Name = "Col2" Value = "80"/>
         <Column Name = "Col3"/>
      </Columns>
   </Row>
</Rows>
'

Extract User and Description values from the root element:

SELECT 
    OneRow.value('@User', 'nvarchar(128)') [UserName],
    OneRow.value('@Description', 'nvarchar(1000)') [Description]
FROM @XML.nodes('/Rows') RowSet(OneRow)

Extract values from inner elements. See how CROSS APPLY is used there.

SELECT 
    OneRow.value('@RowId', 'VARCHAR(15)') RowId,
    Item.value('@Name', 'NVARCHAR(128)') ColumnName,
    Item.value('@Value', 'NVARCHAR(MAX)') Value
FROM @XML.nodes('/Rows/Row') RowSet(OneRow)
    CROSS APPLY OneRow.nodes('Columns/Column') Col(Item)