Traverse a simple xml to extract nodes
In a stored procedure i receive an XML as a parameter. It looks like:
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:
Thanks in advance!
<root>
<id>55408</id>
<id>60179</id>
<id>60640</id>
<id>61787</id>
</root>
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 ;
Thanks in advance!
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.
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.
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);
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial