XQuery

Question

XQuery

gayakr

Old Hand

Points: 382
More actions
November 23, 2009 at 12:54 pm

#98415

Hi
Tried couple of XQueries to dump the following XML parameter into a temp table but in vain.
Can anyone please help. Thank you.
Temp Table structure: (id, measurename,measurevalue,measurestatus)
XML Parameter
---------------
<Response>
<Result>
<ID>139490</tradeID>
<measures>
<name>NPV</measure>
<value>0.0021567782636748153</value>
<status>SUCCESS</status>
</measures>
<measures>
<name>ACCRUAL</measure>
<value>0.005032482615241236</value>
<status>SUCCESS</status>
</measures>
</Result>
<Result>
<ID>183094</ID>
<measures>
<name>NPV</measure>
<value>0.0016431395895004752</value>
<status>SUCCESS</status>
</measures>
<measures>
<name>ACCRUAL</measure>
<value>0.0038339923755011085</value>
<status>SUCCESS</status>
</measures>
<measures>
<name>Cash</measure>
<value>0.0038339923755011085</value>
<status>SUCCESS</status>
</measures>
</Result>
</Response>

Viewing 11 posts - 1 through 10 (of 10 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

First, you need to fix your XML. It has things like a start-tag of "ID" and an end-tag of "/tradeID", and "name" with "/measure". That's not correct XML, and whatever is generating it needs to be fixed.

Once you've done that, something like this will give you what you need:

declare @XML XML ;

select

@XML = '<Response>

<Result>

<ID>139490</ID>

<measures>

<name>NPV</name>

<value>0.0021567782636748153</value>

<status>SUCCESS</status>

</measures>

<measures>

<name>ACCRUAL</name>

<value>0.005032482615241236</value>

<status>SUCCESS</status>

</measures>

</Result>

<Result>

<ID>183094</ID>

<measures>

<name>NPV</name>

<value>0.0016431395895004752</value>

<status>SUCCESS</status>

</measures>

<measures>

<name>ACCRUAL</name>

<value>0.0038339923755011085</value>

<status>SUCCESS</status>

</measures>

<measures>

<name>Cash</name>

<value>0.0038339923755011085</value>

<status>SUCCESS</status>

</measures>

</Result>

</Response>' ;

select

Results.value('(/Result/ID/.)[1]', 'int') as ID,

Result.measure.query('.').value('(/measures/name/.)[1]', 'varchar(50)') as [Name],

Result.measure.query('.').value('(/measures/value/.)[1]', 'varchar(50)') as [Value],

Result.measure.query('.').value('(/measures/status/.)[1]', 'varchar(50)') as [Status]

from

(select

Response.Result.query('.') as Results

from

@XML.nodes('Response/Result') Response (Result)) as Responses

cross apply Results.nodes('/Result/measures') Result (measure) ;

- 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

gayakr Old Hand Points: 382 More actions · Answer 2

Hi

Thank you. Looks like there is some error some where. I corrected the xml format. However, I am not getting the tradeIds(they are all null). Here is my query

The correct XML Format is:

'<tradesResponse>

<status>SUCCESS</status>

</measureResult>

<measure>ACCRUAL</measure>

<status>SUCCESS</status>

</measureResult>

</calypsoTradeResult>

<status>SUCCESS</status>

</measureResult>

<measure>ACCRUAL</measure>

<status>SUCCESS</status>

</measureResult>

</calypsoTradeResult>

</tradesResponse>'

My Query :

select

TradeResults.value('(/calypsoTradeResult/tradeid/.)[1]','BIGINT') AS TradeID,

calypsoTradeResult.measureResult.query('.').value('(/measureResult/measure/.)[1]','varchar(20)') AS Measure,

calypsoTradeResult.measureResult.query('.').value('(/measureResult/value/.)[1]','float') AS MeasureValue,

calypsoTradeResult.measureResult.query('.').value('(/measureResult/status/.)[1]','varchar(20)') AS MeasureStatus

from

( select

tradesResponse.calypsoTradeResult.query('.') as TradeResults

from

@pricedTradesXml.nodes('tradesResponse/calypsoTradeResult') tradesResponse(calypsoTradeResult)

) as TradeResponses

cross apply TradeResults.nodes('/calypsoTradeResult/measureResult') calypsoTradeResult(measureResult)

RESULT OF QUERY

NULLCASH0.00164313958950048SUCCESS

NULLACCRUAL0.00383399237550111SUCCESS

NULLCASH0.00215677826367482SUCCESS

NULLACCRUAL0.00503248261524124SUCCESS

gayakr Old Hand Points: 382 More actions · Answer 3

gayakr

Old Hand

Points: 382

November 23, 2009 at 2:25 pm

#1083166

I got it to work. Thank you, so much

gayakr Old Hand Points: 382 More actions · Answer 4

Hi,

Can you tell me how I can remove the namespace (ns2) and the <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> in the xml before making this query. Thanks a lot

GSquared SSC Guru Points: 260824 More actions · Answer 5

Why remove them? Just include that in the query.

If you really need to remove it, you'll need to convert to varchar(max), and use the Replace function, then convert back to XML.

- 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

gayakr Old Hand Points: 382 More actions · Answer 6

I tried including them in the query this way:

select

---

from

---

(select ---

from @XML.nodes ('ns2:Response/Result') Response (Result)) as Responses)

cross apply Results.nodes('/calypsoTradeResult/measureResult')

But it gives an error 'The name "ns2" does not denote a namespace'. Also, where am I supposed to use this:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

I know that all thsi can be googled and learnt well, but have limited time to complete the task and hence this posting.

One more: I don't see a 'mark as answer' option for these postings as is the case with the Microsft forums. How can I do that here or close the issue.

Thanks a lot for all your help.

LutzM SSC Guru Points: 107049 More actions · Answer 7

You need to add the namespace definition before your query:

;WITH XMLNAMESPACES (

yournamespace 'your namespacefiles'

)

SELECT

If you have problems getting it to work, please post the few two lines of your xml having the namespace defintion and we'll try to help you out.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

gayakr Old Hand Points: 382 More actions · Answer 8

Okay, this is my issue now:

Assume that the the parameter for my stored procedure is in this format:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<ns2:tradesResponse xmlns:ns2="http://www.X.com/xmlschema/response">

<status>SUCCESS</status>

</measureResult>

<status>SUCCESS</status>

</measureResult>

</calypsoTradeResult>

</ns2:tradesResponse>

1st problem: if the UTF is 8, the SP does not get executed at all (even if I remove all code and just give a select 1+2, I get the error 'unable to switch the encoding'

2nd problem: Using the namespace in the query GSquared suggested:

SELECT

Results.value('(/calypsoTradeResult/tradeID/.)[1]', 'bigint') as ID,

calypsoTradeResult.measure.query('.').value('(/measureResult/measure/.)[1]', 'varchar(20)') as [Name],

calypsoTradeResult.measure.query('.').value('(/measureResult/value/.)[1]', 'float') as [Value],

calypsoTradeResult.measure.query('.').value('(/measureResult/status/.)[1]', 'varchar(15)') as [Status]

FROM

(SELECT

tradesResponse.calypsoTradeResult.query('.') as Results

FROM

@pricedTradesXml.nodes('tradesResponse/calypsoTradeResult') tradesResponse (calypsoTradeResult)) as tradesResponses

CROSS APPLY Results.nodes('/calypsoTradeResult/measureResult') calypsoTradeResult (measure) ;

Thank you!

gayakr Old Hand Points: 382 More actions · Answer 9

I have resolved the 'namespace' issue.

Can anyone direct me to teh right links for the encoding problem. Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 10

The most helpful link I found on the web was: http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/ (searching for "sql 2005 Msg 9402").

Interesting part when playing around with the data on my machine: I could easily run it with the UTF-8 encoding, but it would fail with UTF-16.

So I tried the following:

DECLARE @y varchar(max);

DECLARE @x xml;

SELECT @y = 'your xml'

SELECT @x = REPLACE(@y , 'encoding="UTF-16"', '')

-- in your case you'd probably have to change it to eliminate UTF-8 instead of UTF-16...

-- and after that your XQuery statement

Doesn't really look "clean" but I didn't find any other way to make it work. Maybe someone knows a better way...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]