While loop parse table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

While loop parse table

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2012-10-18 : 10:19:40

Hi,

I am trying to run a loop on a table with XML data. The data also has a column with row number.

Example of data
<clientParameters><looking_for>l martin</looking_for><location>fort william</location></clientParameters>

This is the query I have so far, but seems to pick up the same record and keeps outputting.

DECLARE @MyXML XML
DECLARE @intFlag INT
SET @intFlag = 1
SET @MyXML = (SELECT parse FROM ReportRepository.dbo.parse1
WHERE [ROW NUMBER] = @intFlag)
WHILE (@intFlag <=20000)
BEGIN

SELECT
t.u.value('looking_for[1]','varchar(100)') AS looking_for,
t.u.value('location[1]','varchar(100)') AS location,
FROM @MyXML.nodes('clientParameters') t(u)

PRINT @intFlag
SET @intFlag = (@intFlag + 1)
END
GO

Any help would be much appreciated.

Regards,

Roland

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 10:42:47

Can you post the @MyXML from the first two iterations? What I am trying to understand is whether it changes with each iteration, and also whether it has more than one looking_for and location nodes - i.e., whether the data is like 1 or 2 below or something else

-- 1
<clientParameters>
  <looking_for>l martin</looking_for>
  <location>fort william</location>
  <looking_for>l jones</looking_for>
  <location>fort jefferson</location>
</clientParameters>

-- 2
<clientParameters>
  <looking_for>l martin</looking_for>
  <location>fort william</location>
</clientParameters>
<clientParameters>
  <looking_for>l jones</looking_for>
  <location>fort jefferson</location>
</clientParameters>'

rcp
Starting Member

32 Posts

Posted - 2012-10-18 : 10:47:34

The data is like 2.

<clientParameters>
<looking_for>farai white</looking_for>
<location>london</location>
</clientParameters>
<clientParameters>
<looking_for>k clacher</looking_for>
<location>kingston on thames surrey</location>
</clientParameters>
<clientParameters>
<looking_for>farai white</looking_for>
<location>london</location>
</clientParameters>

The parsing is not really the problem, the problem I am having is the looping as it seems to select the first record and repeats it 20000 times. :(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 11:01:07

Change your select query to this, but before you run it in the loop run it for just one iteration:

SELECT
	c2.value('looking_for[1]', 'varchar(100)') AS looking_for,
	c2.value('location[1]', 'varchar(100)') AS location
FROM
	@MyXML.nodes('.') T1(c1)
	CROSS APPLY c1.nodes('/clientParameters') T2(c2)

If that does what you are expecting it to do, you may also want to remove the loop and do it in a set-based query as shown below:

SELECT
	T1.[ROW NUMBER]
	c2.value('looking_for[1]', 'varchar(100)') AS looking_for,
	c2.value('location[1]', 'varchar(100)') AS location
FROM
	ReportRepository.dbo.parse1 T1
	CROSS APPLY T1.parse.nodes('/clientParameters') T2(c2)
WHERE
	T1.[ROW NUMBER] <= 20000;

rcp
Starting Member

32 Posts

Posted - 2012-10-18 : 11:12:30

Hi,

I have tried it and the first bit works fine when I do it on one record. Not sure what you mean with the second.

DECLARE @MyXML XML

SET @MyXML = (SELECT Top 1 parse FROM ReportRepository.dbo.parse1)

SELECT
T1.[ROW NUMBER]
c2.value('looking_for[1]', 'varchar(100)') AS looking_for,
c2.value('location[1]', 'varchar(100)') AS location
FROM
ReportRepository.dbo.parse1 T1
CROSS APPLY T1.parse.nodes('/clientParameters') T2(c2)
WHERE
T1.[ROW NUMBER] <= 20000;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 11:24:54

Almost always, you get better performance from SQL when you use a set-based approach when compared to while loops. The second query I posted is doing exactly the same thing as your entire query including the loop. You can experiment with it by running the query (perhaps after limiting the where clause to a few row_numbers and see what it does).

rcp
Starting Member

32 Posts

Posted - 2012-10-18 : 11:48:04

I have tried various combinations of the code and reduced the number of row numbers and still does not seem to work. Any further suggestions?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 12:48:33

What does it do? Is it giving the wrong results, or is it throwing a syntax error?

If it is giving you the wrong results, that is probably because I misunderstood the data and or the tables. My understanding was that ReportRepository.dbo.parse1 is a table in your database with at least two columns - ROW_NUMBER (which is an integer) and parse (an XML column).

Assuming that that is the case, I made a test table #tmp as standin for your ReportRepository.dbo.parse1 table - the query works as expected against that table. You can copy and paste the code below and see if that is what you are expecting:

CREATE TABLE #tmp([ROW NUMBER] INT, parse XML);

INSERT INTO #tmp VALUES (1,
	'<clientParameters>
	  <looking_for>farai white</looking_for>
	  <location>london</location>
	</clientParameters>
	<clientParameters>
	  <looking_for>k clacher</looking_for>
	  <location>kingston on thames surrey</location>
	</clientParameters>
	<clientParameters>
	  <looking_for>farai white</looking_for>
	  <location>london</location>
	</clientParameters>');

INSERT INTO #tmp VALUES (2,
	'<clientParameters>
	  <looking_for>ABCD white</looking_for>
	  <location>Berlin</location>
	</clientParameters>
	<clientParameters>
	  <looking_for>EFGH clacher</looking_for>
	  <location>Stamford, CT</location>
	</clientParameters>
	<clientParameters>
	  <looking_for>ABCD white</looking_for>
	  <location>New York</location>
	</clientParameters>');


SELECT T1.[ROW NUMBER],
       c2.value('looking_for[1]', 'varchar(100)') AS looking_for,
       c2.value('location[1]', 'varchar(100)') AS location
FROM   #tmp T1
       CROSS APPLY T1.parse.nodes('/clientParameters') T2(c2)
WHERE  T1.[ROW NUMBER] <= 20000;

DROP TABLE #tmp;

rcp
Starting Member

32 Posts

Posted - 2012-10-19 : 05:23:45

Hi, thanks for your help, it turns out that the data has not been stored in the table in an XML format.

Regards,

Roland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources