Parse XML using T-SQL and XQUERY - Searching for specific values

Question

I have some properties of an application being passed to me in XML form. I need to parse out the property by name and assign the value to the appropriate column in my database.

I am currently parsing it out in a SSIS script component but it takes way to long to complete. I was hoping there would be an easy solution for this using XQUERY, but I cannot find what I am looking for.

Here is an example of the xml I am receiving:

<properties>
    <property>
        <name>DISMISS_SETTING</name>
        <value>DEFAULT</value>
    </property>
    <property>
        <name>SHOW_SETTING</name>
        <value>DEFAULT</value>
    </property>
    <property>
        <name>DEFAULT_SETTING</name>
        <value>DEFAULT</value>
    </property>
</properties>

So, if I were looking at the first property element I would assign the value DEFAULT to my DISMISS_SETTING column in my database. Also, it's important to note the order and combinations of the values can come across in no specific order.

Answer 1

Use the value() Method (xml Data Type) to extract a value from your XML. Check for the name you want in a predicate in the XQuery expression.

select 
  @XML.value('(/properties/property[name = "DISMISS_SETTING"]/value/text())[1]', 'nvarchar(100)') as DISMISS_SETTING,
  @XML.value('(/properties/property[name = "SHOW_SETTING"]/value/text())[1]', 'nvarchar(100)') as SHOW_SETTING,
  @XML.value('(/properties/property[name = "DEFAULT_SETTING"]/value/text())[1]', 'nvarchar(100)') as DEFAULT_SETTING

SQL Fiddle

Answer 2

You can do this by extracting the name and value from the xml and pivoting about the name. However, you cannot do this with arbitrary names found at query time. If you need that, you're probably better off removing the PIVOT and just using the name and value columns provided by the inner query.

DECLARE @xml xml

SET @xml = N'<properties>
    <property>
        <name>DISMISS_SETTING</name>
        <value>DEFAULT</value>
    </property>
    <property>
        <name>SHOW_SETTING</name>
        <value>DEFAULT</value>
    </property>
    <property>
        <name>DEFAULT_SETTING</name>
        <value>DEFAULT</value>
    </property>
</properties>'

SELECT     [DISMISS_SETTING], [SHOW_SETTING], [DEFAULT_SETTING]
FROM       (
                SELECT     properties.property.value(N'./name[1]', N'nvarchar(MAX)') AS propertyName
                         , properties.property.value(N'./value[1]', N'nvarchar(MAX)') AS propertyValue
                FROM       @xml.nodes(N'/properties/property') AS properties(property)
           ) AS properties
           PIVOT (MIN(propertyValue) FOR propertyName IN ([DISMISS_SETTING], [SHOW_SETTING], [DEFAULT_SETTING])) AS settings