-2

i am trying to use XQuery in SQL Server, but I need that the index be dynamic:

This is my query:

SELECT 
    CAST(yourXML AS XML).value('(/integracao/item/NumPedido)[1]', 'VARCHAR(MAX)') NumPedido
FROM 
    #xml

What I have tried:

SELECT 
    CAST(yourXML AS XML).value('(/integracao/item/NumPedido)[sql:variable("@index")]', 'VARCHAR(MAX)') NumPedido
FROM 
    #xml

But it returns this error:

XQuery [#xml.yourXML.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Can somebody please help me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Pereira
  • 36
  • 6
  • To quote one of the the close: off-topic options: *Questions seeking debugging help ("**why isn't this code working?**") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it **in the question itself**. Questions without a **clear problem statement** are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve).* – Thom A Mar 05 '18 at 16:17

1 Answers1

1

Look at this post by Mikael Eriksson in this link

declare @Index INT = 2
declare @XML XML = '
<Root>
    <Element>Hello</Element>
    <Element>World</Element>
</Root>
'
SELECT @xml.value ('(/Root/Element)[sql:variable("@Index")][1]', 'varchar(100)')

the [1] is simply a way to tell SQL Server that you need a single node.

hkravitz
  • 1,345
  • 1
  • 10
  • 20