Clever Geek Handbook

T-SQL extracting sql attribute from xml using variable

I am trying to get an XML attribute from an XML variable passing in the name of the attribute I want. The first select statement is great for getting the correct attribute values. However, when I try to set the desired attribute name in the SQL variable, all that is displayed is a string /root/attribs/@id

instead of the actual value. I have tried many variable permutations @path

, all to no avail.

What am I missing here?

DECLARE @XMLString XML = '<root><attribs flags="1" id="test_id" platform="test_platform" /></root>';

SELECT
    flags = x.c.value('(/root/attribs/@flags)[1]', 'nvarchar(50)') ,
    id = x.c.value('(/root/attribs/@id)[1]', 'nvarchar(50)') ,
    [platform] = x.c.value('(/root/attribs/@platform)[1]', 'nvarchar(50)')
FROM
    @XMLString.nodes('/*') x ( c );

DECLARE @Path NVARCHAR(50) = '/root/attribs/@id';
SELECT
    result = x.c.value('(sql:variable("@Path"))[1]', 'nvarchar(50)')
FROM
    @XMLString.nodes('/*') x ( c );
+3


source to share


1 answer


This will allow you to specify the name of the attribute.



DECLARE @XMLString xml = '
  <root>
    <attribs flags="1" id="test_id" platform="test_platform" />
  </root>'

DECLARE @Attribute nvarchar(max) = 'flags'

SELECT
  t.x.value('(/root/attribs/@*[local-name() = sql:variable("@Attribute")])[1]', 'nvarchar(max)')
FROM @XMLString.nodes('/*') t(x)
+4


source






More articles:


All Articles