SQL Server XML Querying with specific value

Question

I have a XML as below in one of the table column of type xml

<fields>
  <field>
    <name>SourceFileName</name>
    <value>ABCD</value>
  </field>
  <field>
    <name>Template</name>
    <value>XYZ</value>
  </field>
</fields>

I need to query the XML to get the value for a specific text in field/name node

i used the below SQL but it doesn't return any data for the second one

SELECT *
FROM   xmltable
WHERE  XMLText.value('(/fields/field/name)**[1]**' ,'varchar(max)') LIKE 
       'Template'

SELECT *
FROM   xmltable
WHERE  XMLText.value('(/fields/field/name)**[1]**' ,'varchar(max)') LIKE 
       'SourceFileName'

whereas the below returns data

SELECT *
FROM   xmltable
WHERE  XMLText.value('(/fields/field/name)**[2]**' ,'varchar(max)') LIKE 
       'Template'

Can somebody help, how do i write a generic query to return data based on the name passed?

How does this work for Update - Update @T Set XMLText.modify('replace value of (value/text())[1] with sql:variable("@NewVal")') FROM @T CROSS APPLY XMLText.nodes('/fields/field') AS XT(XC) where XC.value('(name)[1]', 'varchar(50)') like 'Template' — Algi
– Algi, Commented Nov 24, 2015 at 11:35

marc_s · Accepted Answer · 2015-11-24 11:07:57Z

1

If you want to handle multiple XML elements, you need to use the .nodes() XQuery function.

Try something like this:

SELECT
    Name = XC.value('(name)[1]', 'varchar(50)'),
    [Value] = XC.value('(value)[1]', 'varchar(25)')
FROM
    dbo.XmlTable
CROSS APPLY
    XmlText.nodes('/fields/field') AS XT(XC)

The .nodes() call will create a "pseudo" table XT with a single column XC that contains the XML fragment that is matched by that XPath expression - in your case, you get two rows, each representing one of the <field> elements.

You can now "reach into" those XML fragments and extract the data you need using the .value() calls - and you get the name and value for both <field> elements this way:

answered Nov 24, 2015 at 11:07

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Algi Over a year ago

Thank you marc. It worked like a charm. Kudos for your quick response.

Algi Over a year ago

Another quick question. How does this work for Update - Update @T Set XMLText.modify('replace value of (value/text())[1] with sql:variable("@NewVal")') FROM @T CROSS APPLY XMLText.nodes('/fields/field') AS XT(XC) where XC.value('(name)[1]', 'varchar(50)') like 'Template'

Collectives™ on Stack Overflow

SQL Server XML Querying with specific value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related