Inserting unique identifiers into an xml document

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 2000 Forums

SQL Server Development (2000)

Inserting unique identifiers into an xml document

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-24 : 09:41:48

I have an xml structure like so:

<Order>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem Reference = "123"/>
				<LineItem Reference = "456"/>
				<LineItem Reference = "789"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem Reference = "111"/>
				<LineItem Reference = "222"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
</Order>'

I want to insert an extra attribute into all LineItem nodes. This is to be a unique identifier. The resulting xml would look like:

<Order>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem LineItemID = "1" Reference = "123"/>
				<LineItem LineItemID = "2" Reference = "456"/>
				<LineItem LineItemID = "3" Reference = "789"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem LineItemID = "4" Reference = "111"/>
				<LineItem LineItemID = "5" Reference = "222"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
</Order>

Can anyone give me an indication of how to do this please??

Thankyou

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 09:45:47

Is there a possibility to do this before you export as xml?

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-24 : 11:21:05

Hi Peter

Thanks for your reply.

The requirement has changed a bit, but I shall first explain the reasoning behind it.

We obtain information about a product in xml. This xml is shredded and stored as relational data. The relational data is then serialized as xml and passed back to the application. We are eliminating this process by storing most of the data as an xml data type. This new Basket table replaces a Product and ProductLine table. The Product and ProductLine table tended to have a 1 to 1 relationship unless the product was a 'Bundle' where there could be multiple ProductLines (such as a TV and Stand).

We no longer have a concept of a ProductLine table, as the Product and its ProductLines would be stored as an xml fragment. However, this has caused no end of problems, as the application relies on the ProductLineID for reporting purposes. Therefore, the resolution is to fudge a unique identifier, but it only has to be unique in the context of an order.

An order can have multiple products, with one entry per product. I need to be able to insert an identity for the productlines into the xml. We thought an easier method would be to re-rank all the productLines for an order (basically update the data).

In the example below, a product has been added to the table. Another product has then been added.

create table #temp (id int identity, CatalogueItem xml)
declare @CatalogueItem xml
set @CatalogueItem = '
<Order>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem LineItemID = "1" Reference = "123"/>
				<LineItem LineItemID = "2" Reference = "456"/>
				<LineItem LineItemID = "3" Reference = "789"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
</Order>'
declare @CatalogueItem2 xml
set @CatalogueItem2 = '
<Order>
	<PartOrder>
		<OrderLine>
			<LineItems>
				<LineItem LineItemID = "0" Reference = "345"/>
				<LineItem LineItemID = "0" Reference = "567"/>
				<LineItem LineItemID = "0" Reference = "678"/>
			</LineItems>
		</OrderLine>
	</PartOrder>
</Order>'

insert into #temp (CatalogueItem)
select @CatalogueItem

union all 

select @CatalogueItem2

select Li.line.query('.')as 'LineItem'--, rank() over (order by Li.line.query('.')) as 'Rank'
from #temp
cross apply CatalogueItem.nodes('//LineItems/LineItem')AS Li(line)
drop table #temp

The result would look like:

<LineItem LineItemID = "1" Reference = "123"/>
<LineItem LineItemID = "2" Reference = "456"/>
<LineItem LineItemID = "3" Reference = "789"/>
<LineItem LineItemID = "4" Reference = "345"/>
<LineItem LineItemID = "5" Reference = "567"/>
<LineItem LineItemID = "6" Reference = "678"/>

Oh, and the LineItemID does exist in the xml, but is always 0

Any ways to achieve this will be greatly welcomed.

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-24 : 11:33:39

This bit of code returns what I want:

select Li.line.query('.')as 'LineItem', row_number() over (order by Li.line.value('(./@LineItemID)','int')) as 'Rank'
from #temp
cross apply CatalogueItem.nodes('//LineItems/LineItem')AS Li(line)

LineItem Number
<LineItem LineItemID="0" Reference="345" /> 1
<LineItem LineItemID="0" Reference="567" /> 2
<LineItem LineItemID="0" Reference="678" /> 3
<LineItem LineItemID="1" Reference="123" /> 4
<LineItem LineItemID="2" Reference="456" /> 5
<LineItem LineItemID="3" Reference="789" /> 6

But I now want to update the LineItemID's in the xml to the values in the Number column.

Hearty head pats

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-24 : 15:46:27

This may not be very helpful, but it might give you a bit more to work with.

The following statements will set the LineItemIDs to consecutive numbers within each product. You have to use literals with the modify method so that's why I've written multiple statements, you can either do it this way, or generate the statements as dynamic SQL.

update #temp
set CatalogueItem.modify('replace value of (//LineItems/LineItem/@LineItemID)[1] with "1"')
update #temp
set CatalogueItem.modify('replace value of (//LineItems/LineItem/@LineItemID)[2] with "2"')
update #temp
set CatalogueItem.modify('replace value of (//LineItems/LineItem/@LineItemID)[3] with "3"')
update #temp
set CatalogueItem.modify('replace value of (//LineItems/LineItem/@LineItemID)[4] with "4"')

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-24 : 17:09:53

Is this another great example of the power of XML?

If I were to eat only XML I would starve,
because it would take more energy to digest than it would give me.

rockmoose

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-27 : 04:02:24

Thanks for your replies.....still not sure how to handle my problem, but I have something to work with, so thanks for that!!

Hearty head pats

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 16:52:16

quote:
Originally posted by rockmoose

Is this another great example of the power of XML?

If I were to eat only XML I would starve,
because it would take more energy to digest than it would give me.

rockmoose

You'll love this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=963523&SiteID=1
A great example of where the use of XML is just plain wrong!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-27 : 17:24:17

Great one Sean

!!!

> "However, when I tested that query over 150 000 rows it turned out to be 60(!) times slower than to query over a table where a country name in each language is stored in separate indexed column."

That's also just too good, let's create a table with one column for each language and index it.

The reply:
"...If I try something and its 60 times slower than a perfectly good (in fact the correct) solution..."
Is in fact, not correct either, the correct solution is a M:M relation between Language and Country.

A flexible system is not defined by how many xml% of the resources are consumed by storing and parsing tags.

rockmoose

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 17:29:59

quote:
Originally posted by rockmoose

Great one Sean !!!

> "However, when I tested that query over 150 000 rows it turned out to be 60(!) times slower than to query over a table where a country name in each language is stored in separate indexed column."

That's also just too good, let's create a table with one column for each language and index it.

The reply:
"...If I try something and its 60 times slower than a perfectly good (in fact the correct) solution..."
Is in fact, not correct either, the correct solution is a M:M relation between Language and Country.

A flexible system is not defined by how many xml% of the resources are consumed by storing and parsing tags.

rockmoose

I took "a table where a country name in each language is stored in separate indexed column" to mean a single column with rows for the various languages, and therefore a M:M relation as you described, which is why I said that was the correct solution.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-27 : 17:50:20

I understand, yes it is open to interpretation

I must have had the evil dba/trust no one specs on.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources