T-SQL get value from XML

Question

I am trying to query XML (SSRS) in SQL Server; I want to get the value from the TO in this case.

<ParameterValues>
    <ParameterValue>
        <Name>TO</Name>
        <Value>PacoAT@Stack.com</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>RenderFormat</Name>
        <Value>EXCEL</Value>
    </ParameterValue>
</ParameterValues>

I have tried a few queries but cant seem to get to that level.

*"I have tried a few queries"* and what were those attempts? Why didn't they work? — Thom A, May 10 '22 at 18:41

score 2 · Accepted Answer · answered May 10 '22 at 18:56

You can do it using XQuery

SELECT
  [TO] = t.XmlColumn.value('(ParameterValues/ParameterValue[Name/text() = "TO"]/Value/text())[1]', 'varchar(100)')
FROM YourTable t

/ is a child node navigation. [] is a predicate test on a particular node. So this looks for ParameterValues/ParameterValue which has a Name child with text TO and returns the Value child's text.

Note the use of text() rather than relying on implicit conversion/atomization. Also .value needs to be guaranteed a single result, so needs [1]

Thanks. I Spent way too much time on that :) – user13520940 May 10 '22 at 19:26 — user13520940, May 10 '22 at 19:26

T-SQL get value from XML

1 Answers1

Linked