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