How to select/query xml data from element and selecting sibling value in SQL

Question

I have a SQL Server table called t for example which has 2 columns and lots of rows

ID (PK, int, not null)
Data (XML(.), not null)

In the Data field i have this XML (this I can't change the format of)

<ArrayOfDataAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataAttribute>
    <Name>field1</Name>
    <Value>default-value-example</Value>
</DataAttribute>
<DataAttribute>
    <Name>field2</Name>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>False</Value>
</DataAttribute>
<DataAttribute>
    <Name>field4</Name>
    <Value>example value</Value>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>another value</Value>
</DataAttribute>
</ArrayOfDataAttribute>

I need to return from t the ID and the contents of for the sibling xml item name where it equals field4 or null/empty string if its not there for that row.

So i would end up with this if there were only one row

ID | field4

1 | example value

From reading online it looks like using 'nodes' would be the way to do it but I'm not getting anywhere, the examples I've found online seem to be where people are looking for a specific value. This is the closest I've got:

SELECT  T2.Loc.value('.', 'varchar(max)') 
FROM   t  
CROSS APPLY t.Data.nodes('/*:ArrayOfDataAttribute/*:DataAttribute/Name') as T2(Loc)

any help is gratefully appreciated

Many thanks Richard

Gottfried Lesigang · Accepted Answer · 2020-06-04 14:20:30Z

What you are looking for is a XQuery predicate and how to stuff a value into your XQuery. The first needs brackets ([]), the second can be achieved with the function sql:variable():

Try it like this:

DECLARE @YourTable TABLE(ID INT IDENTITY,[Data] XML);
INSERT INTO @YourTable VALUES
(N'<ArrayOfDataAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataAttribute>
    <Name>field1</Name>
    <Value>default-value-example</Value>
</DataAttribute>
<DataAttribute>
    <Name>field2</Name>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>False</Value>
</DataAttribute>
<DataAttribute>
    <Name>field4</Name>
    <Value>example value</Value>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>another value</Value>
</DataAttribute>
</ArrayOfDataAttribute>');

--A parameter for your search string

DECLARE @searchFor NVARCHAR(MAX)=N'field4'

--the query

SELECT t.ID
      ,t.[Data].value(N'(/ArrayOfDataAttribute
                         /DataAttribute[(Name/text())[1]=sql:variable("@searchFor")]
                         /Value
                         /text())[1]',N'nvarchar(max)') AS field4
FROM @YourTable t;

The XPath can be read as:

Dive into the array of attributes and look for the <DataAttribute> with a <Name> of a given value. There we need the text() within Value.

Hint: Although there are namespaces, the given sample makes no use of any of them. We can omit the declaration in this case...

Collectives™ on Stack Overflow

How to select/query xml data from element and selecting sibling value in SQL

ID | field4

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

ID | field4

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related