OpenXML() Edge Table

[Will]

I need to use OpenXML() to return an Edge Table representation of an xml doc.

Is it safe to assume that OpenXML will always return an Edge Table rowset
that is in exactly the same element/attribute/value order as the orginal
document, from top to bottom?

Thanks for your help ...

[Michael Rys [MSFT]]

Hello Will, OpenXML will return it in that order, but once it is a rowset,
the relational query processor could change the order later for any reason.

Best regards
Michael

[WCM]

Hi Michael,

Thank you for your response. My concern is with the original rowset that
OpenXML() returns: It appears that it is returning an elementName(tagName)
,and then in the very next row it is returning the .xmltext value assigned to
that element, if any value is assigned (if no value then null). It also
seems to return the data sequentially, from top to bottom, based on the
original xml doc. If I can count on the 'sequentially' part, then I can tell
the OpenXML() function to return its result to a #temp table with an identity
column. Then I can always get back to the orignal sequence (the sequence that
matches the original xml doc), if I need to.

However, if the rowset can be returned from OpenXML() out of sequence from
the orignal xml doc, then I have no idea how I would extract the data that I
need. For example, in a UPS xml shipping doc, I need to know that the very
next netAmount matches the just previous packageReferenceNumber. If the
rowset is out of sequence from the start, then I cannot count on that, and I
have no way to connect the packageReferenceNumber to the right netAmount.

Is the orignal rowset, what gets returned directly from openXML(), always in
exact sequence as original xml doc? Or is it sometimes not?

Thanks again for your help ...

[Michael Rys [MSFT]]

Hello WCM,

OpenXML does return it in order. So you should be fine, but please note that
SQL itself does not preserve the order while transfering data through the
query processor.

You also should be able to use the parentid and id columns to have the element
and its value associated together. E.g.,

declare @x nvarchar(1000);
set @x= N'<test><elem1>text1</elem1><elem2><elem3>text2</elem3></elem2></test>';
declare @i int;
exec sp_xml_preparedocument @i output, @x;
select a1.localname as "elem name", a2.text as "elem value"
from OpenXML(@i, '/') a1, OpenXML(@i, '/') a2 -- you may want to use a temp
table to avoid the double OpenXML call.
where a1.id=a2.parentid and a1.nodetype =1;
exec sp_xml_removedocument @i;

HTH
Michael

[WCM]

Michael,

Thank you for that clarification - the method you use would give me a lot
more confidence in the integrity of the OpenXML return. Much appreciated ...

[WCM]

Hi Michael,

There is a lot more to your answer than I saw at first.

It seems that the trick to manipulating edge table data is mainly to join
the edge table to itself based on [ID] and [parentID]. So you can go as far
up or down the node tree as you want to, just by joining to as many instances
of the edge table as you need to make the joins.

Am I reading that right? Thanks ...

Bill

[Joe Fawcett]

"WCM" <W...@discussions.microsoft.com> wrote in message
news:4916CEDE-ED0A-4B06...@microsoft.com...

> Hi Michael,
>
> There is a lot more to your answer than I saw at first.
>
> It seems that the trick to manipulating edge table data is mainly to join
> the edge table to itself based on [ID] and [parentID]. So you can go as
> far
> up or down the node tree as you want to, just by joining to as many
> instances
> of the edge table as you need to make the joins.
>
> Am I reading that right? Thanks ...
>
> Bill
>

I believe that is so. But can I ask why you're doing this? If you have the
XML to begin with why not query it directly instead of converting to a
table? Which version of SQL Server are you using?

--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name

[WCM]

Hi Joe,

Sql Server 2005:
I needed simple way to deal with large UPS .xml files that are not uniform
(i.e., could be a particular child tag exists between one set of <shipment>
tags, but not between others. An Edge table seemed to me the simplest way to
get the shipment data I need (where it exists) so I can work with it in
standard relational way.

To query xml data directly seemed more complicated, but I could be missing
something.

[WCM]

Based on your response, I got to asking myself the same question: Why not
query xml directly? Bascially, I was not doing that because I didn't know
how.

Have since got intro level education in Sql Server 2005 xml methods and how
to use them with XPATH/XQUERY. Openxml() is still simpler for me to 'grok',
but the new 2005 stuff is really powerful, and seems to use far less server
resources.

Thanks for tip ...

Bill