SQL XPath query looking for node value by attribute

Question

I refer to How to get a particular attribute from XML element in SQL Server.

I do not have a particular attribute with the value of the node in it, but rather the I am looking for the value of the node depending on which attribute I am looking for.

I am selecting from an XML column in a table in SQL, but for simplicity sake, I can provide the following example:

DECLARE @xml xml = CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>
<alert>
   <hits>
      <elem name="hit">
         <elem name="scoreFactors">
            <elem name="scoreFactors">
               <elem name="factorId">FactorID 1</elem>
               <elem name="factorDesc">FactorDesc 1</elem>
               <elem name="factorValue">FactorValue 1</elem>
               <elem name="factorScore">FactorScore 1</elem>
               <elem name="factorImpact">FactorImpact 1</elem>
            </elem>
            <elem name="scoreFactors">
               <elem name="factorId">FactorID 2</elem>
               <elem name="factorDesc">FactorDesc 2</elem>
               <elem name="factorValue">FactorValue 2</elem>
               <elem name="factorScore">FactorScore 2</elem>
               <elem name="factorImpact">FactorImpact 2</elem>
            </elem>
         </elem>
       </elem>
   </hits>
</alert>')

SELECT @xml.value('(/alert/hits/elem/elem[@name="factorDesc"])[1]', 'nvarchar(max)')

All I am getting back is a NULL value, but I am expecting 2 rows with FactorDesc 1 and FactorDesc 2 as values, respectively.

What am I doing wrong?

Just a quick note: you don't need that `CONVERT`. You can assign a string constant directly to a variable of type `xml`: `DECLARE @xml xml = ''` — AakashM, Aug 11 '15 at 09:44

marc_s · Accepted Answer · 2015-08-11T09:11:25.663

3

That attribute you're looking for is on the fourth level of <elem> - so you need to use this XPath:

SELECT @xml.value('(/alert/hits/elem/elem/elem/elem[@name="factorDesc"])[1]', 'nvarchar(max)')

or alternatively use this XPath (but beware: this can be a killer of performance, if your XML has a lot of <elem> nodes! You've been warned!)

SELECT @xml.value('(/alert/hits//elem[@name="factorDesc"])[1]', 'nvarchar(max)')

Update: if you want all the nodes with the factorDesc name, you could use something like:

;WITH Shredded AS
(
    SELECT 
        ElemName = xc.value('@name', 'varchar(50)'),
        ElemValue = xc.value('.', 'varchar(50)') 
    FROM
        @xml.nodes('/alert/hits//elem') AS XT(XC)
)
SELECT *
FROM Shredded
WHERE Shredded.ElemName = 'factorDesc'

edited Aug 11 '15 at 09:11

answered Aug 11 '15 at 08:38

marc_s

732,580
175
1,330
1,459

thanks for pointing out that I need to specify all the levels down - or use `//`. Your second example is almost what I need, except that it only returns the first elem where the name attribute is factorDesc. Why isn't it returning a list of all elems where the name attribute is factorDesc? – Ebbs Aug 11 '15 at 08:51
@MrThursday: it's returning just the first one - **because you tell it to** by specifying that `[1]` ... – marc_s Aug 11 '15 at 09:09
@MrThursday: updated my response to handle *all* nodes, too – marc_s Aug 11 '15 at 09:11

score 1 · Answer 2 · answered Aug 11 '15 at 08:39

1

Search in all elem's:

SELECT
    t.value('.','nvarchar(max)')
FROM @xml.nodes('(alert/hits//elem[@name="factorDesc"])') AS t(t)

answered Aug 11 '15 at 08:39

Backs

24,430
5
58
85

Great, thanks! This is exactly what I am looking for except for one thing: If I had to change that query to be able to select all elem's out of an xml column, what would it look like? – Ebbs Aug 11 '15 at 08:44
@MrThursday out of xml column? can you give an example? i'm afaid, i don't understand – Backs Aug 11 '15 at 08:54
I guess it would be a nested select, where I would replace `@xml` with the column name? For the purpouses of my question, I supplied the example xml as variable. but in a more practical sense the xml would come from a a column in a table and would not be hard coded. – Ebbs Aug 11 '15 at 09:07
a query could look like this : SELECT t1.xmlColum FROM table1 t1 – Ebbs Aug 11 '15 at 09:08
@MrThursday, oh, it will look the same, just replace `@xml` with column name – Backs Aug 11 '15 at 09:13

SQL XPath query looking for node value by attribute

2 Answers2