How to loop through xml data that is stored in XML format in SQL Server?

Question

I have a SQL query that currently gets the value that is stored in a column in my database like this:

SELECT 
    @filevalue = (CAST(REPLACE(CAST(de.TRIGGERS_XML_DATA AS VARCHAR(MAX)), 'encoding="utf-16"', '') AS XML).value('(//value)[1]', 'NVARCHAR(max)')), 
    @filecontent = de.ENVIRONMENT_ID
FROM
    dbo.DEPLOYMENT_ENVIRONMENT AS de
WHERE
    de.ENVIRONMENT_ID = CAST(REPLACE(CAST(@filevalue AS numeric(19, 0)), 'encoding="uft-16"', '') AS numeric(19, 0))

The part where it is .value('(//value)[1]') is the problem because sometimes there will be multiple value nodes. I tried to concatenate a counter and the value node together but this came back with the error the value needs to be a string not a varchar when I did this: .value(@ValueNodes, 'NVARCHAR(max)'))

So how can I successfully loop through the xml to get my result? The problem with the way I was doing it is that the first value is not always what I need and will give errors (null values and unable to cast types).

Do you need to store ALL values? Or just loop until you find the desired one? — Josh Part
– Josh Part, Commented Jan 11, 2017 at 16:42
not necessary but the desired one could be any value plus the max nodes I think would be 5 (most I seen was 3) so if it would store them all it would probably be better. — Ciaran Donoghue
– Ciaran Donoghue, Commented Jan 11, 2017 at 16:45

Gottfried Lesigang · Accepted Answer · 2017-01-11 17:52:13Z

1

You can use a deep search XPath, which will find all <value>-nodes wherever they are. You do this with a double // at the beginning. The following will extract all text() within each <value> together with the parent node name:

DECLARE @xml XML=
N'<root>
  <value>test1</value>
  <SomeParent>
    <value>test in parent</value>
    <value>one more in parent</value>
  </SomeParent>
  <value>One more on first level</value>
</root>';

SELECT val.value(N'(./text())[1]','nvarchar(max)') AS TheValueInValue
      ,val.value(N'local-name(..)','nvarchar(max)') AS ParentNode
FROM @xml.nodes(N'//value') AS Each(val)

The result

TheValueInValue           ParentNode
test1                     root
test in parent            SomeParent
one more in parent        SomeParent
One more on first level   root

UPDATE

...as I am getting the xml from a column

This is a blind flight, but you might need something like this:

WITH Casted AS
(
    SELECT 
         TheXML = CAST(REPLACE(CAST(de.TRIGGERS_XML_DATA AS VARCHAR(MAX)), 'encoding="utf-16"', '') AS XML)
        ,de.ENVIRONMENT_ID
    FROM
        dbo.DEPLOYMENT_ENVIRONMENT AS de
)
SELECT Casted.ENVIRONMENT_ID
      ,val.value(N'(./text())[1]','nvarchar(max)') AS TheValueInValue
      ,val.value(N'local-name(..)','nvarchar(max)') AS ParentNode
FROM Casted
CROSS APPLY TheXML.nodes(N'//value') AS Each(val)

edited Jan 11, 2017 at 17:52

answered Jan 11, 2017 at 17:45

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Ciaran Donoghue Over a year ago

cool can I do this basically @xml = de.TRIGGERS_XML_DATA (I know that's now to make that value equal that but just for quickness) for that part as I am getting the xml from a column

Collectives™ on Stack Overflow

How to loop through xml data that is stored in XML format in SQL Server?

1 Answer 1

UPDATE

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related