Using XQuery and looping thru XML data questions.

Question

Using XQuery and looping thru XML data questions.

Ronnie Ang

SSC Rookie

Points: 30
More actions
August 23, 2007 at 12:58 pm

#174406

Hi there
I have a sproc that takes in an XML dataset and I would like to loop through the XML elements in the dataset, convert it to an int and insert them into a table.
How would I loop through the XML dataset to get all the XML data?
Where can I place the condition?
Dont fully understand the Xquery statements.
Thanks for the help!
The XML values passed into the sproc will be as follows:
12345
12346
-- sproc code
declare @TestID int;
declare @i int;
SELECT @TestIDXML =
N'
12345
'
select @i = 1
While @TestID != NULL
BEGIN
SELECT @TestID = x.item.value('./TestCase[1]', 'int')
FROM @TestIDXML.nodes('//TestCaseIDs') AS x(item)
-- insert into table
insert into Table1
values (@TestID)
select @i=@i+1
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

I think your XML markup got whacked - because I can't make out what the markup looks like.

What's the end-goal of looping through the XML values? If it's only to insert them into a table (be it a "for real" one, or a temporary table.) you can do that in a single statement - no loop required.

Try posting your XML data layout, and what you're actually planning on doing with it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ronnie Ang SSC Rookie Points: 30 More actions · Answer 2

Thanks!

This is how the XML will look like

12345

12346

The sproc will read in the XML as a parameter.

Then I would like to insert the TestCaseID value into a table as an Int value.

Greg Walker Old Hand Points: 369 More actions · Answer 3

It's still eating your XML, but assuming it looks like:

You can use:

INSERT INTO <table> (field) SELECT T.T.value('.[1]', 'INT') FROM @XML.nodes('/TestCaseID') AS T(T)

Should do what you're trying to do without a loop.

(edited to fix case, since XML stuff is case sensitive)

Greg Walker
DBA, ExpenseWatch.com