Querying XML data from a SQL Server table

Question

I've tried every suggestion on the SO threads related to this topic, but still not getting what I need. I have an XML column named SC. This is on SQL Server 2014 (in case it matters). There is only one row in the table, and the XML data in column SC contains the following snippet...

<SC_ROOT>
  <COMPONENTS>
    <COMPONENT>
      <NAME>Status A Detection</NAME>
      <PROPERTIES>
        <COMP_ENABLED>True</COMP_ENABLED>
      </PROPERTIES>
    </COMPONENT>
    ...
  </COMPONENTS>
</SC_ROOT>

I would like to return a table with <NAME> and <COMP_ENABLED> for only those items where the <NAME> contains "Detection". If there is already a good example of this please point me to it? Thanks in advance!

Do you want to return only the XML where the name contains Detection, or do you want to shred that XML into a table? — Dan Field
– Dan Field, Commented Feb 29, 2016 at 14:07
I want to return the values without the XML. So that would be something like: 'Status A Detection','True' — Skatterbrainz
– Skatterbrainz, Commented Feb 29, 2016 at 17:06

marc_s · Accepted Answer · 2016-02-29 19:22:40Z

4

Try this:

SELECT
    Name = xc.value('(NAME)[1]', 'varchar(50)'),
    CompEnabled = xc.value('(PROPERTIES/COMP_ENABLED)[1]', 'varchar(10)')
FROM 
    dbo.YourTable
CROSS APPLY
    SC.nodes('/SC_ROOT/COMPONENTS/COMPONENT') AS XT(XC)
WHERE
    xc.value('(NAME)[1]', 'varchar(50)') LIKE '%Detection'

The .nodes() call bascially returns a "virtual table" with a table alias XT which has one column (alias XC) that contains the XML fragment that corresponds to the XPath expression - basically the <COMPONENT> XML fragment. You reach into that to extract the detailed info you need

Update: if your XML looks like this:

<COMPONENT>
  <NAME>Status A Detection</NAME>
  <PROPERTIES NAME="COMP_ENABLED" VALUE="True" />
</COMPONENT>

then use this code to get the results:

SELECT
    Name = xc.value('(NAME)[1]', 'varchar(50)'),
    CompEnabled = xc.value('(PROPERTIES[@NAME="COMP_ENABLED"]/@VALUE)[1]', 'varchar(10)')
FROM 
    dbo.YourTable
CROSS APPLY
    SC.nodes('/SC_ROOT/COMPONENTS/COMPONENT') AS XT(XC)
WHERE
    xc.value('(NAME)[1]', 'varchar(50)') LIKE '%Detection'

edited Feb 29, 2016 at 19:22

answered Feb 29, 2016 at 14:28

marc_s

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

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

6 Comments

Skatterbrainz Over a year ago

I'm looking to return all of the component entries which have a name that ends with "Detection" and the corresponding "comp_enabled" column value. Some will be "true" and some "false".

marc_s Over a year ago

@Skatterbrainz: that's what my answer is doing - or not??

Skatterbrainz Over a year ago

I would think so, but I'm getting no results when I run it. What would be the code to just dump all of the <NAME> nodes as a column of values?

marc_s Over a year ago

@Skatterbrainz: just remove the second item from the SELECT

Skatterbrainz Over a year ago

Bingo! That gets me closer. Still working on the property set

|

Dan Field · Accepted Answer · 2016-02-29 14:15:19Z

So assuming that COMPONENT can repeat, and you want to just select the COMPONENT nodes where NAME LIKE '%Detection%', the following should work:

DECLARE @xml xml = '<SC_ROOT>
  <COMPONENTS>
    <COMPONENT>
      <NAME>Status A Detection</NAME>
      <PROPERTIES>
        <COMP_ENABLED>True</COMP_ENABLED>
      </PROPERTIES>
    </COMPONENT>  
    <COMPONENT>
      <NAME>Status B Other</NAME>
      <PROPERTIES>
        <COMP_ENABLED>True</COMP_ENABLED>
      </PROPERTIES>
    </COMPONENT>  
    <COMPONENT>
      <NAME>Status C Detection</NAME>
      <PROPERTIES>
        <COMP_ENABLED>True</COMP_ENABLED>
      </PROPERTIES>
    </COMPONENT>    
  </COMPONENTS>
</SC_ROOT>'

SELECT X.Component.query('.')
FROM @xml.nodes('/SC_ROOT/COMPONENTS/COMPONENT') as X(Component)
WHERE CAST(x.Component.query('NAME/text()') AS NVARCHAR(100)) LIKE '%Detection%'

output:

<COMPONENT><NAME>Status A Detection</NAME><PROPERTIES><COMP_ENABLED>True</COMP_ENABLED></PROPERTIES></COMPONENT>
<COMPONENT><NAME>Status C Detection</NAME><PROPERTIES><COMP_ENABLED>True</COMP_ENABLED></PROPERTIES></COMPONENT>

That will get you the raw XML fragments. If you want to shred that down to a table, you'd have to modify the columns you're selecting and the .query() in the SELECT. This will work no matter how many times NAME repeats in a single COMPONENT, as long as at least one has the word 'Detection' in it.

If NAME can only occur once, or you only care about the first instance, you could also do:

SELECT X.Component.query('.')
FROM @xml.nodes('/SC_ROOT/COMPONENTS/COMPONENT[contains(NAME[1], "Detection")]') as X(Component)

Which would give the same output.

Collectives™ on Stack Overflow

Querying XML data from a SQL Server table

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related