Select nodes in T-SQL XML by name given in a variable

Question

I'm trying to select nodes in XML document by provided name.

declare @xdoc xml = '

    <data>
        <box><id>1</id><weight>10</weight></box>
        <tube><id>2</id><weight>20</weight></tube> <!-- Should be skipped -->
        <box><id>3</id><weight>30</weight></box>
    </data>

'

declare @node nvarchar(100)='box'

select
    ref.value('id[1]','bigint') as Id,
    ref.value('weight[1]','bigint') as Weight
from @xdoc.nodes('/data/*[local-name()=[sql:variable("@node")]]') as xdata(ref)
-- WORKS FINE:from @xdoc.nodes('/data/*[local-name()="box"]') as xdata(ref)

and it gives me error message:

XQuery [nodes()]: Syntax error near '[', expected a step expression.

How do I access local variable in xml.nodes()? Or may be there is some better way to achieve it?

zx485 · Accepted Answer · 2018-02-08 20:34:58Z

1

Your XPath expression misses a * around /data/ and has some superfluous [] around sql:variable("@node"):
it should be

select
  ref.value('id[1]','bigint') as Id,
  ref.value('weight[1]','bigint') as Weight
from @xdoc.nodes('/data/*[local-name()=sql:variable("@node")]') as xdata(ref)

EDIT: added correction mentioned in comment by author.

edited Feb 8, 2018 at 20:34

answered Feb 8, 2018 at 20:21

zx485

29.1k28 gold badges55 silver badges65 bronze badges

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

2 Comments

dmay Over a year ago

Ah, copypaste is not my friend :) Correct full query looks like /data/*[local-name()=sql:variable("@node")]

zx485 Over a year ago

@dmay: Yes, You're expression makes more sense. I hope you don't mind if I put it in the answer for future reference.

Collectives™ on Stack Overflow

Select nodes in T-SQL XML by name given in a variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related