CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

How to iterate through all rows of an xml variable

I have an xml variable like

declare @xmlvar xml
create table tempxml(col1 xml)
insert into tempxml
exec someproc

select * from tempxml

After this code tempxml contains the data:

<row eid="1" ename="jason" />
<row eid="2" ename="mike" />
<row eid="3" ename="ron" />

Now I want to iterate a loop and fetch the eid for each row and need to call a stored procedure with eid as a parameter. I.e. I need to do something like this

// Dummy code
While eid is not null
 // fetch eid from xml 
 exec somesp @eid = eid
Next

Upvotes: 1

Views: 2004

Answers (2)

marc_s
marc_s

Reputation: 754628

You can try something like this:

-- declare table variable
DECLARE @EIDTable TABLE (RowEID INT)

-- fill table variable from XML variable
INSERT INTO @EIDTable(RowEID)
    SELECT
         row.value('(@eid)[1]', 'int')
    from 
         @xmlvar.nodes('/row') AS XmlRow(ROW)

-- declare single EID to process            
DECLARE @EID INT

-- fill single EID    
SELECT TOP  1 @EID = RowEID FROM @inputtable

-- loop while not NULL
WHILE @eid IS NOT NULL
BEGIN
    -- execute your stored procedure here with @eid as a parameter

    -- remove that EID that's been processed
    DELETE FROM @inputtable
    WHERE RowEID = @eid

    -- grab next EID from temporary table variable and loop    
    SET @EID = NULL
    SELECT TOP  1 @EID = RowEID FROM @inputtable
END

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

declare @xml xml = 
'<row eid="1" ename="jason" />
 <row eid="2" ename="mike" />
 <row eid="3" ename="ron" />'

-- @T table to hold the eid's from the xml
declare @T table (ID int identity, eid int)

insert into @T
select
  r.value('@eid', 'int')
from @xml.nodes('row') n(r) 

declare @eid int
declare @CurrentID int = 1

-- Fetch first eid
select @eid = eid
from @T
where ID = @CurrentID

while @@rowcount = 1
begin
  --exec SP with @eid here

  --Fetch next eid
  set @CurrentID = @CurrentID + 1

  select @eid = eid
  from @T
  where ID = @CurrentID
end

Upvotes: 2

Related Questions