Search code examples
sql-serverxmlt-sqlxml-parsingwebserver

SQL XML parsing using a attribute value supplied by another field in the same row

Context: I'm scraping some XML form descriptions from a Web Services table in hopes of using that name to identify what the user has inputted as response. Since this description changes for each step (row) of the process and each product I want something that can evaluate dynamically.

What I tried: The following was quite useful but it returns a dynamic attribute query result in it's own field ans using a coalesce to reduce the results as one field would lead to it's own complications: Get values from XML tags with dynamically specified data fields

Current Attempt: I'm using the following code to generate the attribute name that I will use in the next step to query the attribute's value:

case when left([Return], 5) = '<?xml' 
    then lower(cast([Return] as xml).value('(/response/form/*/@name)[1]','varchar(30)')) 
    else '' 
    end as [FormRequest]

And as part of step 2 I have used the STUFF function to try and make the row-level query possible

case when len(FormRequest)>0
    then stuff( ',' + 'cast([tmpFormResponse] as xml).value(''(/wrapper/@' + [FormRequest] + ')[1]'',''varchar(max)'')', 1, 1, '') 
    else '' 
    end as [FormResponse]

Instead of seeing 1 returned as my FormReponse feild value for the submit attribute (please see in yellow below) it's returning the query text -- cast([tmpFormResponse] as xml).value('(/wrapper/@submit)1','varchar(max)') -- instead (that which should be queried). query result

How should I action the value method so that I can dynamically strip out the response per row of XML data in tmpFormResponse based on the field value in the FormRequest field?

Thanx

Solution

  • You can check this out:

    DECLARE @xml XML=
N'<root>
<SomeAttributes a="a" b="b" c="c"/>
<SomeAttributes a="aa" b="bb" c="cc"/>
</root>';

DECLARE @localName NVARCHAR(100)='b';

SELECT sa.value(N'(./@*[local-name()=sql:variable("@localName")])[1]','nvarchar(max)')
FROM @xml.nodes(N'/root/SomeAttributes') AS A(sa)