Very interesting Post. I was looking for some thing like this for a very long time.

I am not sure if you have covered this already, but the above approach is very useful in handling comma seperated strings and implementing array like functions.

For Ex:

DECLARE @Value NVARCHAR(MAX) SET @Value = '1,a,3,s,5,c,z,f'

SET @x = '' + '' + REPLACE( @Value, ',', '') + '' +

Now, you have the XML used in your post.

BTW, can you cover few more functions like position(), if there are.

@anand, I recently added many more articles to the 'XQuery Labs' series. You may be able to find more functions there: http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

I love this solution. Very creative. I'm trying to see how it would work to take it a step further. I have xml data in a column and in that xml there are elements I want to number, but also want to extract the child elements also numbered.

So if the XML in the table (we'll call it scheduleInfo) is in a field called scheduleXML, is there a way to make it work.

So one sample record in the xml would look like:

<Schedules>
  <Name value="Bob">
    <Class>Math</Class>
    <Class>English</Class>
    <Class>Science</Class>
  </Name>
  <Name value="Joe">
    <Class>History</Class>
    <Class>Math</Class>
    <Class>English</Class>
  </Name>
</Schedules>

What I'd like to get in my output is (position of the name, name, position of the class, class):

1,Bob,1,Math
1,Bob,2,English
1,Bob,3,Science
2,Joe,1,History
2,Joe,2,Math
2,Joe,3,English

Any thoughts on how to do this? Seems I may have to break out the xml into another table to get the position, name and then the xml of the classes, and then possibly go from there? Very new to XML in SQL Server so trying to wrap my head around this. I couldn't even get this solution properly written to pull from a table instead of a variable (may have just missed something) so going further down is really twisting my brain.

Thanks

Is this what you are looking for?

DECLARE @x XML 
SELECT @x = '
<Schedules>
  <Name value="Bob">
	<Class>Math</Class>
	<Class>English</Class>
	<Class>Science</Class>
  </Name>
  <Name value="Joe">
	<Class>History</Class>
	<Class>Math</Class>
	<Class>English</Class>
  </Name>
</Schedules>'

SELECT
	p.number as Position,
	x.value('@value[1]','VARCHAR(10)') AS Name,
	p2.Number AS Sr,
	c.value('.','VARCHAR(10)') AS Class
FROM
master..spt_values p
CROSS JOIN master..spt_values p2
CROSS APPLY @x.nodes('/Schedules/Name[position()=sql:column("p.number")]') n(x) 
CROSS APPLY x.nodes('Class[position()=sql:column("p2.number")]') b(c)
where p.type = 'p' AND p2.type = 'P'
/*
Position    Name       Sr          Class
----------- ---------- ----------- ----------
1           Bob        1           Math
1           Bob        2           English
1           Bob        3           Science
2           Joe        1           History
2           Joe        2           Math
2           Joe        3           English
*/