SQL XML Select: Need to get the index of the node

OK

I am using this as a split function and need to insert the split into a table.

I don't want to put the results into a temp table if I don't have to.

DECLARE @value varchar(50)
SET @value = '9|5|22|298'
      
SET @xml = N'<root><r>' + replace(@value,'|','</r><r>') + '</r></root>'
SELECT
    --I NEED THE INDEX OF THE NODE HERE
      r.value('.','varchar(50)')
FROM @xml.nodes('//root/r') as records(r)
WHERE LEN(r.value('.','varchar(50)')) >0

--I Want to do something like this

INSERT INTO TABLE (order, valueID)
SELECT
    --I NEED THE INDEX OF THE NODE HERE
      r.value('.','varchar(50)')
FROM @xml.nodes('//root/r') as records(r)
WHERE LEN(r.value('.','varchar(50)')) >0

Thanks for the help
skillileaAsked:
Who is Participating?
 
Saurabh BhadauriaConnect With a Mentor Commented:
Do it like this..
DECLARE @value varchar(50)
DECLARE @xml XML 
SET @value = '9|5|22|298'

      
SET @xml = N'<root><r>' + replace(@value,'|','</r><r>') + '</r></root>'
SELECT 
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq,
      r.value('.','varchar(50)') 
FROM @xml.nodes('//root/r') as records(r)
WHERE LEN(r.value('.','varchar(50)')) >0

--I Want to do something like this

INSERT INTO TABLE (order, valueID)
SELECT 
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq,
      r.value('.','varchar(50)') 
FROM @xml.nodes('//root/r') as records(r)
WHERE LEN(r.value('.','varchar(50)'))

Open in new window



Thanks,
Saurabh
0
 
skillileaAuthor Commented:
Awesome!

Just what I needed...tnx tons
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.