T-SQL retrieving sql attribute from xml using variable

Question

I am trying to retrieve an XML attribute from an XML variable passing in the name of the desired attribute. The first select statement works just fine retrieving the correct attribute values. However, when I try to set within a SQL variable the desired attribute name, all that is displayed is the string /root/attribs/@id instead of the actual value. I have tried numerous permutations of the @path variable, 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 );

I don't think you can use a variable to specify your xquery, only for values that you refer to within it. — muhmud
– muhmud, Commented Feb 15, 2013 at 21:52

jdl · Accepted Answer · 2013-02-16 00:14:36Z

4

This will allow you to specify the attribute name.

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)

answered Feb 16, 2013 at 0:14

jdl

1,1048 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL retrieving sql attribute from xml using variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related