XMl Data into Table - SQL Server Forums

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

Transact-SQL (2005)

XMl Data into Table

Author

Topic

sateesh.sqldba
Starting Member

25 Posts

Posted - 2010-10-31 : 03:07:59

Hi Experts,
i am poor in xml i am trying to learn would please tel me how to insert this data into Table

<?xml version="1.0" encoding="utf8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Header>
<wsu:Timestamp xmlns:wsu="http://schemas.xmlsoap.org/ws/2002/07/utility">
<wsu:Created>20090312T11:23:02Z</wsu:Created>
<wsu:Expires>20090312T11:28:02Z</wsu:Expires>
</wsu:Timestamp>
</soap:Header>
<soap:Body>
<GetAvailableToursXMLResponse xmlns="http://webapi.globusandcosmos.com/">
<GetAvailableToursXMLResult xmlns="http://webapi.globusandcosmos.com/AvailableTours.xsd">
<Tours>
<Tour>
<TourNumber>BYC</TourNumber>
<Brand>COSMOS</Brand>
<Name>COSTA DEL SOL</Name>
</Tour>
<Tour>
<TourNumber>BYD</TourNumber>
<Brand>COSMOS</Brand>
<Name>CROATIA</Name>
</Tour>
<Tour>
<TourNumber>BYDU</TourNumber>
<Brand>COSMOS</Brand>
<Name>CROATIA WITH SEAVIEW ROOMS</Name>
</Tour>
<Tour>
<TourNumber>BYE</TourNumber>
<Brand>COSMOS</Brand>
<Name>ESTORIL COAST & THE ALGARVE</Name>
</Tour>
<Tour>
<TourNumber>BYF</TourNumber>
<Brand>COSMOS</Brand>
<Name>COSTA RICA</Name>
</Tour>
</Tours>
</GetAvailableToursXMLResult>
</GetAvailableToursXMLResponse>
</soap:Body>
</soap:Envelope>

i want TourNumber,Brand,Name into my sql table please help me out

Thanks
sateesh

sateesh

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-03 : 01:55:03

[code]
Declare @xml XML

SELECT @xml =
'<?xml version="1.0" encoding="utf-8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Header>
<wsu:Timestamp xmlns:wsu="http://schemas.xmlsoap.org/ws/2002/07/utility">
<wsu:Created>20090312T11:23:02Z</wsu:Created>
<wsu:Expires>20090312T11:28:02Z</wsu:Expires>
</wsu:Timestamp>
</soap:Header>
<soap:Body>
<GetAvailableToursXMLResponse xmlns="http://webapi.globusandcosmos.com/">
<GetAvailableToursXMLResult xmlns="http://webapi.globusandcosmos.com/AvailableTours.xsd">
<Tours>
<Tour>
<TourNumber>BYC</TourNumber>
<Brand>COSMOS</Brand>
<Name>COSTA DEL SOL</Name>
</Tour>
<Tour>
<TourNumber>BYD</TourNumber>
<Brand>COSMOS</Brand>
<Name>CROATIA</Name>
</Tour>
<Tour>
<TourNumber>BYDU</TourNumber>
<Brand>COSMOS</Brand>
<Name>CROATIA WITH SEAVIEW ROOMS</Name>
</Tour>
<Tour>
<TourNumber>BYE</TourNumber>
<Brand>COSMOS</Brand>
<Name>ESTORIL COAST and THE ALGARVE</Name>
</Tour>
<Tour>
<TourNumber>BYF</TourNumber>
<Brand>COSMOS</Brand>
<Name>COSTA RICA</Name>
</Tour>
</Tours>
</GetAvailableToursXMLResult>
</GetAvailableToursXMLResponse>
</soap:Body>
</soap:Envelope>'

;WITH XMLNAMESPACES('http://webapi.globusandcosmos.com/AvailableTours.xsd' AS result,
'http://webapi.globusandcosmos.com/' AS response,
'http://schemas.xmlsoap.org/soap/envelope/' AS xtop)
select
n.value('result:TourNumber[1]','varchar(200)') TourNumber,
n.value('result:Brand[1]','varchar(200)') Brand,
n.value('result:Name[1]','varchar(200)') Name
FROM @xml.nodes('/xtop:Envelope/xtop:Body/response:GetAvailableToursXMLResponse/result:GetAvailableToursXMLResult/result:Tours/result:Tour') as x(n)

[/code]

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-11-03 : 02:10:02

or simpler


;WITH XMLNAMESPACES('http://webapi.globusandcosmos.com/AvailableTours.xsd' AS result) 
select
n.value('result:TourNumber[1]','varchar(200)') TourNumber,
n.value('result:Brand[1]','varchar(200)') Brand,
n.value('result:Name[1]','varchar(200)') Name
FROM @xml.nodes('//result:Tours/result:Tour') as x(n)

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources