Re: Recursive Xml Quert

It may not be possible to do the recursion you want, but it is not clear exactly what recursion you want to do. The initializer for your recursive CTE refers to an element that is not in the document.

SELECT VIRT.node.query('./Items') as Items, VIRT.node.value('Key[1]', 'int') as [Key],
VIRT.node.value('Description[1]', 'varchar(max)') as Description,
0 as Level
FROM @xml.nodes('/Item/Item') as VIRT(node)

There is no /Item/Item in the document.

What do you expect as output? Are you trying to get a table of all of the key and descriptions in the document? This would do that...

DECLARE @xdata xml
SET @xdata = '<Root>........</Root>'
SELECT VIRT.node.value('Key[1]', 'int') as [Key],
VIRT.node.value('Description[1]', 'varchar(max)') as Description
FROM @xdata.nodes('
for $i in (//Item)
return $i
') AS VIRT(node)



Dan



I am trying to perform a recursive query in Sql 2005 that essentially
parses an xml string.

When I run the query I get the following error message:
Msg 107, Level 15, State 1, Line 83
The column prefix 'CTE_ITEMS' does not match with a table name or
alias name
used in the query.
Msg 9506, Level 16, State 1, Line 83
The XMLDT method 'nodes' can only be invoked on columns of type xml.
Am I wrong to believe that recursing xml can be done or am I going
about it wrong?

Sample code is below.

Thanks,
David Archuleta
DECLARE @xml xml

SET @xml = cast ('<?xml version="1.0" encoding="utf-8"?>
<Root>
<Item><Key>-1</Key>
<Description>Test -1</Description>
<Items>
<ItemCollection>
<Item>
<Key>0</Key>
<Description>Test 0</Description>
<Items>
<ItemCollection>
<Item><Key>1</Key>
<Description>Test 1</Description>
<Items>
<ItemCollection />
</Items>
</Item>
<Item>
<Key>2</Key>
<Description>Test 2</Description>
<Items>
<ItemCollection />
</Items>
</Item>
<Item>
<Key>3</Key>
<Description>Test 3</Description>
<Items>
<ItemCollection />
</Items>
</Item>
<Item>
<Key>11</Key>
<Description>Test 11</Description>
<Items>
<ItemCollection>
<Item>
<Key>12</Key>
<Description>Test 12</Description>
<Items>
<ItemCollection />
</Items>
</Item>
</ItemCollection>
</Items>
</Item>
</ItemCollection>
</Items>
</Item>
</ItemCollection>
</Items>
</Item>
</Root>' as xml);
/*
--TEST QUERY
SELECT
VIRT.n.query('./Items') as Items,
VIRT.n.value('Key[1]', 'int') as [Key],
VIRT.n.value('Description[1]', 'varchar(max)') as Description
FROM @xml.nodes('//Item') as VIRT(n);
*/

WITH CTE_ITEMS (Items, [Key], Description, Level)
AS
(
SELECT
VIRT.node.query('./Items') as Items,
VIRT.node.value('Key[1]', 'int') as [Key],
VIRT.node.value('Description[1]', 'varchar(max)') as Description,
0 as Level
FROM @xml.nodes('/Item/Item') as VIRT(node)
UNION ALL
SELECT
VIRT.node.query('./Items') as Items,
VIRT.node.value('Key[1]', 'int') as [Key],
VIRT.node.value('Description[1]', 'varchar(max)') as Description,
Level + 1
FROM
CTE_ITEMS.Items.nodes('//ItemCollection') as VIRT(node)
)
SELECT * FROM CTE_ITEMS



.

Relevant Pages

  • Re: Timing Rescordset
    ... The recursive function I am using first check to see if it has something, ... avoid levels of recursion that lack substantial advantage. ... XML authors use the same node name in a different heirarchy? ... To reconstruct, you'll have to query the db for each level, in much the same ...
    (microsoft.public.data.ado)
  • Re: Date Format Conversion?
    ... I didn't say customer sales and contact information, ... >And I won't have to go throw hundreds of spreadsheets to find the ... >much with XML; i prefer CSV or TSV or something else; even binary ... it what you need to do is recursion. ...
    (microsoft.public.excel)
  • Vista IE7 Recussion Error
    ... When you call this xml file in XP or FireFox using IE6 you get the ... template recursion. ... When you call this xml file in VISTA using IE7 on a linovo X60s Intel ... the stack overflow occurred starting again at Level 1076. ...
    (comp.text.xml)
  • Re: Timing Rescordset
    ... I just read data into arrays, ... The problem was that it took hours to read a big xml file. ... I'm guessing that Bob found that node_text is exposed as a child node the ... avoid levels of recursion that lack substantial advantage. ...
    (microsoft.public.data.ado)
  • Re: Timing Rescordset
    ... The problem was that it took hours to read a big xml file. ... then use a recordset to store them. ... avoid levels of recursion that lack substantial advantage. ... parameter query will not accept the node name as a parameter as a table ...
    (microsoft.public.data.ado)
Loading