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 = '<?xml version="1.0" encoding="UTF-8"?><alert></alert>' — AakashM
– AakashM, Commented Aug 11, 2015 at 9:44

marc_s · Accepted Answer · 2015-08-11 09:11:25Z

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, 2015 at 9:11

answered Aug 11, 2015 at 8:38

marc_s

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

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

3 Comments

Ebbs Over a year ago

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?

marc_s Over a year ago

@MrThursday: it's returning just the first one - because you tell it to by specifying that [1] ...

marc_s Over a year ago

@MrThursday: updated my response to handle all nodes, too

Backs · Accepted Answer · 2015-08-11 08:39:34Z

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, 2015 at 8:39

Backs

25k5 gold badges66 silver badges92 bronze badges

5 Comments

Ebbs Over a year ago

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?

Backs Over a year ago

@MrThursday out of xml column? can you give an example? i'm afaid, i don't understand

Ebbs Over a year ago

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 Over a year ago

a query could look like this : SELECT t1.xmlColum FROM table1 t1

Backs Over a year ago

@MrThursday, oh, it will look the same, just replace @xml with column name

Collectives™ on Stack Overflow

SQL XPath query looking for node value by attribute

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related