Link to home
Start Free TrialLog in
Avatar of skillilea
skillilea

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skillilea
skillilea

ASKER

Awesome!

Just what I needed...tnx tons