We help IT Professionals succeed at work.

Traverse a simple xml to extract nodes

soozh
soozh asked
on
In a stored procedure i receive an XML as a parameter.  It looks like:

<root>
  <id>55408</id>
  <id>60179</id>
  <id>60640</id>
  <id>61787</id>
</root>

Open in new window


I need to do some processing on each <id> tag.  My first thought was to place them in a temporary table and then use a cursor to read each one and do the neccessary processing.

However there may be a way to traverse the XML in a cursor like scenario so i do not need the temporary table and you are welcome to suggest a solution.

How would extract each <id> tag and place it in a temporary table?

I am thinking:

Traverse the xml, extract <id> tag, and create a temp table

Create cursor to traverse table

While cursor returns data DO
begin
  Process the current <id>.
end ;

Open in new window


Thanks in advance!
Comment
Watch Question

Senior Developer
CERTIFIED EXPERT
Commented:
It depends on what you like to do and what SQL dialect you use.

E.g. in T-SQL for small lists you can simply parse it in memory and for larger lists it may give you an performance boost using an index temporary table.

DECLARE @Paramaeter XML = N'
<root>
  <id>55408</id>
  <id>60179</id>
  <id>60640</id>
  <id>61787</id>
</root>
';

SELECT R.ID.value('.', 'INT') AS ID
FROM   @Paramaeter.nodes('/root/id') R(ID);

Open in new window

You can use the SELECT as subquery either to fill a temporary table or use it directly for JOIN's.

And whether you need a cursor or not, depends on the kind of processing you need to do. When you think you'll need a cursor, then you should

a) reevaluate the processing strategy, maybe an external tools (SSIS for SQL Server) would be the better choice.
b) reevaluate your logic, maybe you can express on a set-based approach.
c) reevaluate your data model, maybe its inaccurate, thus forcing the use of a cursor instead of a set-based solution.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.