'T-SQL get value from XML

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>[email protected]</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.

sql-serverxmltsqlxquery-sql


Solution 1:[1]

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]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions