Handling this kind of XML doc.

[stiej]

Hi,

I am having trouble handling my XML document. I'm new to SQL and XML anyway and would appreciate any help! My XML doc is basically the following...

<root>
<developments ID=&quot;xx&quot; Ref=&quot;xx&quot; Name=&quot;xx&quot;>
<Criteria ID = &quot;xx&quot; Name=&quot;xx&quot; Value=&quot;xx&quot; Type=&quot;xx&quot;/>
<Criteria ID = &quot;xx&quot; Name=&quot;xx&quot; Value=&quot;xx&quot; Type=&quot;xx&quot;/>
<Criteria ID = &quot;xx&quot; Name=&quot;xx&quot; Value=&quot;xx&quot; Type=&quot;xx&quot;/>
.
.
.
</developments>
</root>

This is how the XML document is given to me, prepared elsewhere by an external company. My trouble is that I don't know how to handle the fact that the sub elements of <developments> element repeat (i.e there is more than one criteria element) and also the attributes or name/value pairs, ID, Name, Value and Type, reside in every Criteria element. The only values I get output are the first instances of the name/value pairs that the OPEMXML comes across.

So...

1) how do I get each and every value from this XML doc?

2) how can I miss certain attributes? i.e I don't want to pick up the values for any ID except the first one located in the development tag. I also wish to ignore the Type attribute too.

The examples I've looked at in TransactSQL Help are all short and have unique names within tags etc which is far easier but this document is giving me a lot of headaches! Any help very much appreciated.

 

[ChrisHunt]

What are you using to read the XML? What are you trying to do with it once you've done so?

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[stiej]

To read the XML I'm using MS SQL Server 2000 function called sp_xp_preparedocument and sp_xp_removedocument. The &quot;workhorse&quot; used to parse the xml though is OPENXML, which takes a handle to an in-memory XML representation of the actual XML file and goes through it. The problems occur once it has read the last attribute of the <development> tag. Since attributes from there on share the same name, I only get the data that it picked up from the attributes in the <development> tag. Besides some attributes, like I said, I don't want to pick up. The first ID attribute in each development is needed, others aren't. The Type attribute can be ignored to, so how do i choose which things I want to read and which I don't?

 

[palbano]

so you are really dealing with SQL Servers XML interface implementation. If you don't get an answer here you might try one of the SQL Server forums here at Tek-Tips.

-pete