CODEWITHSUNDEEP
sqlxmlt-sql
David
David

Reputation: 273

SQL XML Query with Name and Value Pairs

I have a string column in a SQL table that contains Parameters. For example:

<ParameterValues>
    <ParameterValue>
        <Name>Sub</Name>
        <Value>E23DD735-DCF4-4878-8810-EAC2594C58A4</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>OperatorId</Name>
        <Value>1001</Value>
    </ParameterValue>
</ParameterValues>

Can anyone help me with a SQL Query to, for example, return the value from the Sub field where the OperatorId value is 1001. In this case, the answer would be 'E23DD735-DCF4-4878-8810-EAC2594C58A4'.

Upvotes: 1

Views: 1114

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

Use this to test it:

DECLARE @tbl TABLE (ID INT IDENTITY,YourXmlAsString NVARCHAR(MAX));
INSERT INTO @tbl VALUES
    ('<ParameterValues>
    <ParameterValue>
        <Name>Sub</Name>
        <Value>E23DD735-DCF4-4878-8810-EAC2594C58A4</Value>
    </ParameterValue>
    <ParameterValue>
        <Name>OperatorId</Name>
        <Value>1001</Value>
    </ParameterValue>
</ParameterValues>');

A direct call to get exactly the one value your are looking for:

WITH Casted AS (SELECT ID,CAST(YourXmlAsString AS XML) AS TheXml FROM @tbl AS t)
SELECT TheXml.value(N'(/ParameterValues/ParameterValue[Name="Sub"]/Value)[1]','uniqueidentifier') AS TheID
FROM Casted
WHERE TheXml.exist(N'/ParameterValues/ParameterValue[Name="OperatorId"]/Value[text()="1001"]')=1

returns

E23DD735-DCF4-4878-8810-EAC2594C58A4

Try it like this to get a derived table:

WITH Casted AS (SELECT ID,CAST(YourXmlAsString AS XML) AS TheXml FROM @tbl AS t)
SELECT ID 
      ,pv.value(N'(Name/text())[1]',N'nvarchar(max)') AS Parameter_Name
      ,pv.value(N'(Value/text())[1]',N'nvarchar(max)') AS Parameter_Value
FROM Casted 
CROSS APPLY Casted.TheXml.nodes(N'/ParameterValues/ParameterValue') AS A(pv)

The result

ID  Parameter_Name  Parameter_Value
1   Sub             E23DD735-DCF4-4878-8810-EAC2594C58A4
1   OperatorId      1001

Upvotes: 2

Related Questions