Cursor for spliting t-sql @xml variable on elements level

Question

I need to define some cursor for spliting t-sql @xml variable on elements level into different @xml(s).

for example:

<root>
<element id=10/>
<element id=11/>
<element id=12/>
<element id=13/>
</root>

so that get the following values inside of tsql cursor:

<root><element id=10/><element id=11/></root>

then

<root><element id=12/><element id=13/></root>

and so on where n number of elements pro cursor loop.

Answer 1

Well, you can use the build-in functions for manipulating XML. For example, the following statement:

DECLARE @XML XML = N'<root><element id="10"/><element id="11"/><element id="12"/><element id="13"/></root>'

SELECT  ROW_NUMBER() OVER (ORDER BY T.c) 
       ,T.c.query('.')
FROM @XML.nodes('root/element') T(c)

will give you all elements preserving the order they have in the XML structure:

Then you can stored this result and build separate smaller XML variables.

---

For different elements you can use * like this:

DECLARE @XML XML = N'<root><element1 id="10"/><element2 id="11"/><element3 id="12"/><element4 id="13"/></root>'

SELECT  ROW_NUMBER() OVER (ORDER BY T.c) 
       ,T.c.query('.')
FROM @XML.nodes('root/*') T(c)