Loop through xml tags in sql - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Loop through xml tags in sql

Author

Topic

moodi_z
Starting Member

38 Posts

Posted - 2009-06-22 : 02:26:20

Hi, I have this xml example:


declare @FileHandle as int
declare @xmlstring as nvarchar(max)

set @xmlstring = 
'<POInfo>
	<PO>
		<POID>100</POID>
		<RouteID>P1</RouteID>
		<CatalogID>J001</CatalogID>
		<RequestedQty>100</RequestedQty>
		<LotID/>
		<Priority>1</Priority>
		<UserID>Administrator</UserID>
	</PO>	
	<POTags>	
		<Tag ID="Test1">
			<Value>10</Value>
		</Tag>
		<Tag ID="Test2">
			<Value>20</Value>
		</Tag>
		<Tag ID="combo1">
			<Value>aaa</Value>
		</Tag>
		<Tag ID="combo2">
			<Value>p1</Value>
		</Tag>
		<Tag ID="checkTest">
			<Value>false</Value>
		</Tag>
		<Tag ID="intTest">
			<Value>11</Value>
		</Tag>
		<Tag ID="realTest">
			<Value>50.5</Value>
		</Tag>
	</POTags>
	<PO>
		<POID>200</POID>
		<RouteID>P2</RouteID>
		<CatalogID>F002</CatalogID>
		<RequestedQty>200</RequestedQty>
		<LotID/>
		<Priority>2</Priority>
		<UserID>Administrator</UserID>
	</PO>	
	<POTags>	
		<Tag ID="Test1">
			<Value>10</Value>
		</Tag>
		<Tag ID="Test2">
			<Value>20</Value>
		</Tag>
		<Tag ID="combo1">
			<Value>bbb</Value>
		</Tag>
		<Tag ID="combo2">
			<Value>p2</Value>
		</Tag>
		<Tag ID="checkTest">
			<Value>true</Value>
		</Tag>
		<Tag ID="intTest">
			<Value>11</Value>
		</Tag>
		<Tag ID="realTest">
			<Value>50.5</Value>
		</Tag>
	</POTags>
</POInfo>'


DECLARE @Out varchar(100)
EXECUTE sp_InsertPODetails @xmlstring, @Err_Description = @Out OUTPUT
SELECT @Out

I need to loop through the tags for every PO. What I'm doing write now is to loop through the PO's:


SELECT	POID,
			RouteID,
			CatalogID,
			RequestedQty,
			LotID,
			Priority,
			UserID

	INTO	#TMP1

	FROM	OPENXML (@FileHandle, 'POInfo/PO')

	WITH (	POID Varchar(255) 'POID', 
			RouteID varchar(255) 'RouteID' , 
			CatalogID Varchar(255) 'CatalogID',
			RequestedQty Varchar(25) 'RequestedQty',
			LotID Varchar(255) 'LotID',
			Priority Varchar(25) 'Priority',
			UserID varchar(255) 'UserID' 
		)

DECLARE PO_Cursor SCROLL CURSOR FOR 
SELECT POID, RouteID, CatalogID, RequestedQty, Priority, UserID
FROM #TMP1

OPEN PO_Cursor

FETCH FIRST FROM PO_Cursor 
INTO @POID, @RouteID, @CatalogID, @RequestedQty, @Priority, @UserID

....

and then gets the tags:

SELECT ID, Value
	INTO	#TMP2
	FROM OPENXML (@FileHandle, 'POInfo/POTags/Tag')
	WITH (ID Varchar(50), Value varchar(255) 'Value')

But the problem that I'm getting the tags for all PO's, and for each PO I'm looping all the tags.

I'm not sure that I'm in the right direction. How does it can be performed.

Thanks in advance.

moodi_z
Starting Member

38 Posts

Posted - 2009-06-22 : 04:30:25

I also have a problem with nested cursors and the value of @@FETCH_STATUS. For now I used another variable to store the value of first cursor @@FETCH_STATUS.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 04:43:50

What is your expected result for the above posted sample data?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 04:45:40

One big flaw in your xml document design is that how can you tell which "potag" element belongs to which "po" element?
Do you rely on order of presence in file?

E 12°55'05.63"
N 56°04'39.26"

moodi_z
Starting Member

38 Posts

Posted - 2009-06-22 : 05:27:55

Thanks for your reply.
So, what you are saying that I need to assign an identifier for each POTags section. I didn't know that I have to, I thought that the xml parser handle it.

Expected result is inserting all PO's in one table and their Tags into another.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 05:38:23

However, if you change your XML layout to below, you can easily access all your data in a simple fashion.

DECLARE	@data XML

SET	@Data = '
<POInfo>
	<PO>
		<POID>100</POID>
		<RouteID>P1</RouteID>
		<CatalogID>J001</CatalogID>
		<RequestedQty>100</RequestedQty>
		<LotID/>
		<Priority>1</Priority>
		<UserID>Administrator</UserID>
		<POTags>	
			<Tag ID="Test1">
				<Value>10</Value>
			</Tag>
			<Tag ID="Test2">
				<Value>20</Value>
			</Tag>
			<Tag ID="combo1">
				<Value>aaa</Value>
			</Tag>
			<Tag ID="combo2">
				<Value>p1</Value>
			</Tag>
			<Tag ID="checkTest">
				<Value>false</Value>
			</Tag>
			<Tag ID="intTest">
				<Value>11</Value>
			</Tag>
			<Tag ID="realTest">
				<Value>50.5</Value>
			</Tag>
		</POTags>
	</PO>
	<PO>
		<POID>200</POID>
		<RouteID>P2</RouteID>
		<CatalogID>F002</CatalogID>
		<RequestedQty>200</RequestedQty>
		<LotID/>
		<Priority>2</Priority>
		<UserID>Administrator</UserID>
		<POTags>	
			<Tag ID="Test1">
				<Value>10</Value>
			</Tag>
			<Tag ID="Test2">
				<Value>20</Value>
			</Tag>
			<Tag ID="combo1">
				<Value>bbb</Value>
			</Tag>
			<Tag ID="combo2">
				<Value>p2</Value>
			</Tag>
			<Tag ID="checkTest">
				<Value>true</Value>
			</Tag>
			<Tag ID="intTest">
				<Value>11</Value>
			</Tag>
			<Tag ID="realTest">
				<Value>50.5</Value>
			</Tag>
		</POTags>
	</PO>	
</POInfo>
'

SELECT		p.n.value('POID[1]', 'INT') AS POID,
		p.n.value('RouteID[1]', 'VARCHAR(10)') AS RouteID,
		p.n.value('CatalogID[1]', 'VARCHAR(6)') AS CatalogID,
		t.n.value('@ID', 'VARCHAR(200)') AS TagID,
		t.n.value('.', 'VARCHAR(200)') AS TagValue
FROM		@data.nodes('POInfo/PO') AS p(n)
CROSS APPLY	n.nodes('POTags/Tag') AS t(n)

E 12°55'05.63"
N 56°04'39.26"

moodi_z
Starting Member

38 Posts

Posted - 2009-06-29 : 08:11:23

Hi,
Thanks a lot, it really helped me and sorry for the delay, I was in vacation.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 08:14:01

Thank you for your feedback, and you're welcome!

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources