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.
CodePudding user response:
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]