CODEWITHSUNDEEP
sql-serverxml
Ciaran Donoghue
Ciaran Donoghue

Reputation: 828

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

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).

Upvotes: 1

Views: 1233

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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)

Upvotes: 1

Related Questions