Parse XML stored in SQL row - MS SQL Sever

Question

I have a table (user_table) with 10 users and one of the column has name "XML_PATH". The XML_PATH contains XML file which looks like this :

<Column>
         <Column Name="user_time_zone" DataType="String">GMT</Column>
         <Column Name="user_time_type" DataType="String">24hrs</Column>
         ...
         <Column Name="user_time_country" DataType="String">India</Column>
</Column>

I need to know if it is possible for me to write an SQL query and get USER_TIME_TYPE node's value as a result.

Answer 1

You can do this with XPATH. Example:

DECLARE @Value XML = '
<Column>
         <Column Name="user_time_zone" DataType="String">GMT</Column>
         <Column Name="user_time_type" DataType="String">24hrs</Column>
         <Column Name="user_time_country" DataType="String">India</Column>
</Column>
'

SELECT @Value.query('/Column/Column[@Name=''user_time_type'']/text()')

Output:

24hrs

If the column type is XML you can use the query method to perform XPATH queries on the column. To test you XPATH expression you can use XPath tester

SQLFiddle

If you have column you can simply do the following:

SELECT XML_DATA.query('/Column/Column[@Name=''user_time_type'']/text()')
FROM user_date

Or if the column type is not XML

SELECT cast(XML_DATA as XML).query('/Column/Column[@Name=''user_time_type'']/text()')
FROM user_date