sql-serverxmlt-sqlxquery-sql

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

Solution

  • 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]