11

I have an XML field that I know will have at least one "ChildNode" in it, but possibly more. I am trying to make a loop in T-SQL that will get the XML of each ChildNode as a VarChar(1000) and do some logic with it. When I try the following...

...
SET @intIterator=1 

SET @strValue = (SELECT XMLField.value('(/RootNode/ParentNode/ChildNode)[' + CAST(@intIterator AS VARCHAR(2)) + ']', VARCHAR(1000)) WHERE PrimaryKeyField=@intID)

WHILE LEN(@strValue) > 0
   BEGIN

      --LOGIC with @strValue not shown.
      @intIterator = @intIterator + 1
      @strValue = (SELECT XMLField.value('(/RootNode/ParentNode/ChildNode)[' + CAST(@intIterator AS VARCHAR(2)) + ']', VARCHAR(1000)) WHERE PrimaryKeyField=@intID)

   END

I get the following error: The argument 1 of the xml data type method "value" must be a string literal.

I know that it's blowing up when I try to use the @intIterator in the value method as it wants a string literal instead of a variable, but then how can I iterate through the child nodes one by one in T-SQL?

NoAlias
  • 9,218
  • 2
  • 27
  • 46

2 Answers2

19

I don't have any idea what your XML looks like, but you probably have to use a different approach - don't try to iterate and loop and stuff like that - instead use the .nodes() function in XQuery:

SELECT 
    Child.value('(SomeElement)[1]', 'int'),
    Child.value('(SomeOtherElement)[1]', 'Varchar(50)')
FROM
    XMLField.nodes("/RootNode/ParentNode/ChildNode") AS N(Child)

That basically leaves the iterating / looping to XQuery and you don't have to mess around with indices or anything like that at all.....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Quite right. You're in a relational database. Think set-based logic, not looping and procedural logic. – Chris Dickson Feb 15 '11 at 16:29
  • Sorry @marc_s, can you edit your post. There is an extra comma in ` 'Varchar(50)'),`. I can not edit because it is only 1 symbol. – new2ios Dec 07 '16 at 14:57
9

There still could be a need to query sub elements which the answer to this question would not solve. You can just use sql:variable to satisfy nodes() requirement of a string literal argument to query sub elements of a specific node iteratively.

DECLARE @iterator = 1

SELECT 
    Child.value('(SomeElement)[1]', 'int'),
    Child.value('(SomeOtherElement)[1]', 'Varchar(50)'),
FROM
    XMLField.nodes("/RootNode/ParentNode[sql:variable("@iterator")]/ChildNode") AS N(Child)
frimley
  • 212
  • 5
  • 9
  • XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *' – user2338150 Aug 19 '19 at 10:05