CODEWITHSUNDEEP
sqlsql-serverxml
John
John

Reputation: 273

SQL get value from XML in tag, by tag value

I have the following XML:

<Main>
    <ResultOutput>
        <Name>TEST1</Name>
        <Value>D028</Value>
    </ResultOutput>
    <ResultOutput>
        <Name>TEST2</Name>
        <Value>Accept</Value>
    </ResultOutput>
    <ResultOutput>
        <Name>TEST3</Name>
        <Value />
    </ResultOutput>
</Main>

What I want is to get the value of the <value> tag in SQL.

Basically want to say get <value> where <Name> has the value of TEST1, as an example

This is what I have at the moment, but this depends on the position of the XML tag:

XMLResponse.value(Main/ResultOutput/Value)[5]', nvarchar(max)')

Upvotes: 0

Views: 1556

Answers (2)

Charlieface
Charlieface

Reputation: 71579

The best way to do this is not to put extra where .value clauses, but to do it directly in XQuery.

Use [nodename] to filter by a child node, you can even nest such predicates. text() gets you the inner text of the node:

XMLResponse.value('(/Main/ResultOutput[Name[text()="TEST1"]]/Value/text())[1]', 'nvarchar(max)')

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

Below is an example using the sample XML in your question. You'll need to extend this to add namespace declarations and the proper xpath expressions that may be present in your actual XML as your query attempt suggests.

SELECT ResultOutput.value('Value[1]', 'nvarchar(100)')
FROM @xml.nodes('Main/ResultOutput') AS Main(ResultOutput)
WHERE ResultOutput.value('Name[1]', 'nvarchar(100)') = N'TEST1';

Upvotes: 0

Related Questions