XML: I've tried every syntax possible.

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

Transact-SQL (2008)

XML: I've tried every syntax possible.

Author

Topic

PhilSchmidt
Starting Member

5 Posts

Posted - 2011-02-08 : 09:03:57

I'd really appreciate it if someone could help me out.

I've got the following XML stored in XMLField in Atable.

<?xml version="1.0" encoding="utf-16"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<RateResponse xmlns="http://werner/ent/freitrater/ratingengine">
<RateResult id="0" ClientCode="XZXZXZX" ClientNetRate="false" Clientid="999" CarrierCode="ABCD" Carrierid="99" CarrierMode="Q">
<freightbillconstraints>
<freightbillconstraint id="0" ConstraintName="Equipment" Constraintid="3" ConstraintValue="mv">
<UIErrorsColl />
</freightbillconstraint>
</freightbillconstraints>
</RateResult>
</RateResponse>
</soap:Body>
</soap:Envelope>

I'm trying to select the value for CarrierCode which is "ABCD".

This doesn't work (My latest attempt) . . .

SELECT
XMLField.value('/Envelope/Body/RateResponse/RateResult/@CarrierCode[1]', 'nvarchar(10)') AS CarrierCode
FROM ATable

I get an error . . .
Msg 2389, Level 16, State 1, Line 2
XQuery [ATable.XMLField.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-08 : 11:09:51

I fixed the error (needs parentheses):

SELECT XMLField.value('(/Envelope/Body/RateResponse/RateResult/@CarrierCode)[1]', 'nvarchar(10)') AS CarrierCode
FROM ATable

But I can't figure out why it's not returning the value for CarrierCode. I've tried variations that work, my guess is the xmlns declaration in the RateResponse part is not complete (compare to other xmlns declarations in previous section).

PhilSchmidt
Starting Member

5 Posts

Posted - 2011-02-08 : 11:31:03

Thanks for the reply.

Yea, I had tried that too but since I was getting NULL back for the value I figured the syntax wasn't correct.

Hmmmmm . . . . if that's the correct syntax, then question is now, "what the heck is wrong with the XML?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-08 : 11:42:01

Looks like it is a namespace problem, this works (changes in red):

<?xml version="1.0" encoding="utf-16"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<freight:RateResponse xmlns:freight="http://werner/ent/freitrater/ratingengine">
<RateResult id="0" ClientCode="XZXZXZX" ClientNetRate="false" Clientid="999" CarrierCode="ABCD" Carrierid="99" CarrierMode="Q">
<freightbillconstraints>
<freightbillconstraint id="0" ConstraintName="Equipment" Constraintid="3" ConstraintValue="mv">
<UIErrorsColl />
</freightbillconstraint>
</freightbillconstraints>
</RateResult>
</freight:RateResponse>
</soap:Body>
</soap:Envelope>

SELECT XMLField.value('declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
declare namespace freight="http://werner/ent/freitrater/ratingengine";
(/soap:Envelope/soap:Body/freight:RateResponse/RateResult/@CarrierCode)[1]', 'nvarchar(10)') AS CarrierCode
FROM ATable

If the RateResponse node does NOT declare a namespace, it returns NULL for any element or attribute at or below that node. The .value method won't return any elements without the "declare namespace" directives added.

PhilSchmidt
Starting Member

5 Posts

Posted - 2011-02-08 : 12:05:01

That is awesome. I would have never figured that out on my own. I don't remember seeing any examples using the .value method with the declare namespace declarations.

THANK YOU!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-08 : 12:08:08

quote:
I don't remember seeing any examples using the .value method with the declare namespace declarations

It's kinda buried in the Books Online entry, but it's there: http://msdn.microsoft.com/en-us/library/ms178030.aspx

PhilSchmidt
Starting Member

5 Posts

Posted - 2011-02-08 : 13:55:03

Thanks for putting me on the right path.

I've discovered a way to do it without modifying the original XML . . .

SELECT XMLField.value('declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
declare default element namespace "http://werner/ent/freitrater/ratingengine";
(/soap:Envelope/soap:Body/RateResponse/RateResult/@CarrierCode)[1]', 'nvarchar(10)') AS CarrierCode
FROM ATable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources