Pass SQL parameter value to select xml data

Question

Code:

DECLARE @dataxml XML = CONVERT(xml ,'<Parentnode><childnode><id>1</id></childnode></Parentnode>')

DECLARE @childnode  VARCHAR(50) 
SET @childnode = (SELECT DISTINCT 
                      r.value('fn:local-name(.)', 'nvarchar(50)') as t 
                  FROM @dataxml.nodes('//Parentnode/*') AS records(r))

SELECT @childnode

SELECT
   t.value('id[1]', 'int') AS id 
FROM
   @dataxml.nodes('/Parentnode/*[local-name(.)=sql:variable("@childnode")]/*') AS XD(t)

Output is returned as NULL, but it should comes with id value in xml

What's wrong in this code?

marc_s · Accepted Answer · 2014-07-03 09:47:12Z

1

You just need to remove the /* characters at the end of your last line, like this:

@dataxml.nodes('/Parentnode/*[local-name(.)=sql:variable("@childnode")]') AS XD(t)

Also I had to remove the $ character at the end of the FROM keyword, but I'm guessing that could just be a typo. I tested this is SQL Server 2012.

edited Jul 3, 2014 at 9:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 3, 2014 at 9:31

XN16

5,88916 gold badges52 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Pass SQL parameter value to select xml data

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