XML Node Query

Question

XML Node Query

lucky-80472

SSCertifiable

Points: 7793
More actions
March 24, 2009 at 11:34 am

#128886

Hello,
I was given a task to have a query which gets the information from the xml data in a table format.
I'm herewith providing the sample information (attached) and also the query which I constructed. It is returning the parent node information but only 1 child node information instead of 2.
The information provided is only a sample and I need to see that this query works for all the different language codes. If it is static then I would have hardcoded but it is not so.
This is the result I'm expecting from the sql query
The parent record should have
ProductId Rank StartDate EndDate
========= ==== ========= =======
324054689 1 2009-03-05 2009-04-06
The child record should have
ProductId LanguageCode TempId TempLabel
========= ============ ====== =========
324054689 en Price Exceeded
324054689 eng Price Exceed
I would appreciate if anyone can point me in the right direction.
Thanks

Lucky

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Your query is using "nodes()" on "productRef", but there's only one productRef in your XML sample.

I've attached a query that will get the second result you want. The main change is changing nodes from productRef to temp.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 2

GSquared (3/24/2009)
Your query is using "nodes()" on "productRef", but there's only one productRef in your XML sample.
I've attached a query that will get the second result you want. The main change is changing nodes from productRef to temp.
Does that help?

Hello GSqaured,

Thanks for the query and that helps.

So how should I work in case of the point you raised? if there are multiple "productRef" in the xml data. I'm still learning as this is a new topic for me. Will it be possible for me to construct a single query or else a join like the one I mentioned in my first attachment.

Once again thanks for the solution.

Lucky

GSquared SSC Guru Points: 260824 More actions · Answer 3

If there can be more than one productRef, what you'll want is one XML query that uses nodes on that, and another one that uses nodes on the temps, and then you'll need to join those somehow. I'm not sure what the correct join criteria would be. Maybe don't join them and have it as two separate queries for two separate purposes?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

yghwang Grasshopper Points: 14 More actions · Answer 4

Hello,

Please help! I have one table (TBL1) has only one XML column (COL1) contains one xml element. The value has changed when I run the Xquery.

select COL1 from TBL1:

=================

<v>

</v>

</vs>

Thanks!

When I run the following query the value changed:

======================================

SELECT X.Y.query('.') XML_Element

FROM TBL1

CROSS APPLY TBL1.COL1.nodes('/vs/v') as X(Y)

<v>

<n>VOLGROSS1</n>

</v>

yghwang Grasshopper Points: 14 More actions · Answer 5

Please correct the return result:

<v>

</v>

Thanks.