SQL Newbie needs help retrieving data from xml

Question

Post reply

SQL Newbie needs help retrieving data from xml

steve-lauzon

SSC Veteran

Points: 283
More actions
April 15, 2011 at 1:55 pm

#97937

Hi... I'm a newbie in SQL and I need to retrieve a field from a XML file
Here is the file (summary)
<OTA_HotelDescriptiveContentNotifRQ xmlns="http://www.opentravel.org/OTA/2005/12" EchoToken="E384570476" TimeStamp="2011-03-14T18:50:45.014+01:00" Target="Production" Version="2005" PrimaryLangID="en">
<HotelDescriptiveContents ChainCode="117" HotelCode="93685" BrandName="RESERVIT" BrandCode="58">
<HotelDescriptiveContent CurrencyCode="CAD" LanguageCode="fr">
</HotelDescriptiveContent>
</HotelDescriptiveContents>
</OTA_HotelDescriptiveContentNotifRQ>
I need to retrieve the BrandName attribute in the HotelDescriptiveContents (which in this case is RESERVIT)
This is the code I'm using... the value always returns null
DECLARE @idoc int
DECLARE @doc varchar(max)
set @doc = '<OTA_HotelDescriptiveContentNotifRQ xmlns="http://www.opentravel.org/OTA/2005/12" EchoToken="E384570476" TimeStamp="2011-03-14T18:50:45.014+01:00" Target="Production" Version="2005" PrimaryLangID="en">
<HotelDescriptiveContents ChainCode="117" HotelCode="93685" BrandName="RESERVIT" BrandCode="58">
<HotelDescriptiveContent CurrencyCode="CAD" LanguageCode="fr">
</HotelDescriptiveContent>
</HotelDescriptiveContents>
</OTA_HotelDescriptiveContentNotifRQ>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT BrandName
FROM OPENXML (@idoc, '/OTA_HotelDescriptiveContentNotifRQ/HotelDescriptiveContents',1)
WITH (ChainCode varchar(10),
HotelCode varchar(20),
BrandName varchar(20),
BrandCode varchar(20))
Can somebody help?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Since it's a typed xml document you need to use a namespace declaration.

Instead of using OpenXml, I'd recommend using XQuery instead:

DECLARE @xml XML

SELECT @xml='

<OTA_HotelDescriptiveContentNotifRQ xmlns="http://www.opentravel.org/OTA/2005/12" EchoToken="E384570476" TimeStamp="2011-03-14T18:50:45.014+01:00" Target="Production" Version="2005" PrimaryLangID="en">

<HotelDescriptiveContents ChainCode="117" HotelCode="93685" BrandName="RESERVIT" BrandCode="58">

<HotelDescriptiveContent CurrencyCode="CAD" LanguageCode="fr">

</HotelDescriptiveContent>

</HotelDescriptiveContents>

</OTA_HotelDescriptiveContentNotifRQ>

'

;

WITH xmlnamespaces(

DEFAULT 'http://www.opentravel.org/OTA/2005/12')

SELECT

c.value('@BrandName[1]','varchar(20)')

FROM @xml.nodes('OTA_HotelDescriptiveContentNotifRQ/HotelDescriptiveContents') AS T(c)

If you'd like to read more about XQuery, I recommend to have a look at Jacob Sebastians blog.

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]

steve-lauzon SSC Veteran Points: 283 More actions · Answer 2

Wow! Works like a charm 🙂

Thanks a million... you just saved me several hours of trial & error 🙂

I will definitely get the book

Thanks again

Steve