XML problem

Question

Hi,

I have an xml like this

 

declare @x xml
set @x='<root>
<BookingPassengers>
<BookingPassenger>
<Name>R</Name>
<PassengerID>12</PassengerID>
<PassengerFees>
<SSRCode>PBAB</SSRCode>
</PassengerFees>
<PassengerFees>
<SSRCode>IMNX</SSRCode>
</PassengerFees>
</BookingPassenger>
<BookingPassenger>
<Name>K</Name>
<PassengerID>32</PassengerID>
<PassengerFees>
<SSRCode>VGMX</SSRCode>
</PassengerFees>
</BookingPassenger>
</BookingPassengers>
</root>'

 

I want o/p like this

PassengerID ,  SSRCode

12                    PBAB

12                    IMNX

32                    VGMX

---

Thanks and regards, Rishabh

Answer 1

Hi Rishabh, try this...

declare @handle int
declare @x xml
set @x='<root>
<BookingPassengers>
<BookingPassenger>
<Name>R</Name>
<PassengerID>12</PassengerID>
<PassengerFees>
<SSRCode>PBAB</SSRCode>
</PassengerFees>
<PassengerFees>
<SSRCode>IMNX</SSRCode>
</PassengerFees>
</BookingPassenger>
<BookingPassenger>
<Name>K</Name>
<PassengerID>32</PassengerID>
<PassengerFees>
<SSRCode>VGMX</SSRCode>
</PassengerFees>
</BookingPassenger>
</BookingPassengers>
</root>'

EXEC sp_xml_preparedocument @handle OUTPUT, @x

SELECT *
FROM
OPENXML ( @handle, '/root/BookingPassengers/BookingPassenger/PassengerFees', 1)
WITH ( PassengerID     int         '../PassengerID',
       SSRCode         varchar(20) 'SSRCode'
      )
    
EXEC sp_xml_removedocument @handle

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 2

 SELECT 
 x.y.value('../PassengerID[1]', 'INT') [PassengerID]
,x.y.value('SSRCode[1]','VARCHAR(4)') [SSRCode] 
FROM @x.nodes('/root/BookingPassengers/BookingPassenger/PassengerFees') AS x(y) 

---

Jon

Answer 3

Try this:

SELECT
  Tab.Col.value('(../PassengerID)[1]','int') AS PassengerID,
    Tab.Col.value('(SSRCode)[1]','nvarchar(50)') AS SSRCode
FROM @x.nodes('/root/BookingPassengers/BookingPassenger/PassengerFees') Tab(Col)

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 4

Rishabh,

Both XQuery and OPENXML are valid solutions. For performance trade-offs, see this blog post...

http://sqlserverdownanddirty.blogspot.com/2010/12/shredding-xml-with-openxml-nodes-part_19.html

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 5

DECLARE

 

@hdoc int

EXEC

 

sp_xml_preparedocument @hdoc OUTPUT, @X

declare

 

@booking table (PassengerId int, SSRCode char(5))

insert

 

into @booking (PassengerId, SSRCode)

SELECT

 

PassengerId, SSRCode
FROM OPENXML (@hdoc, 'root/BookingPassengers/BookingPassenger/PassengerFees/SSRCode',1)

WITH

 

(
PassengerId int '../../PassengerID/text()',
SSRCode char(5) '.'
)

select

 

* from @booking

Answer 6

Hi,

Thanks ALL,

I tried almost everything except double dots(..)  :-)

---

Thanks and regards, Rishabh

Answer 7

Hi,

One More Concern what if i want to go two level down

is there any thing like 3 dots

---

Thanks and regards, Rishabh

Answer 8

../../

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 9

As you are using the XML data-type you are probably using SQL 2005 or 2008 (or 2008 R2).  Therefore you should probably be using the xml datatype type and it's methods, eg

DECLARE @x XML
SET @x = '<root>
 <BookingPassengers>
  <BookingPassenger>
   <Name>R</Name>
   <PassengerID>12</PassengerID>
   <PassengerFees>
    <SSRCode>PBAB</SSRCode>
   </PassengerFees>
   <PassengerFees>
    <SSRCode>IMNX</SSRCode>
   </PassengerFees>
  </BookingPassenger>
  <BookingPassenger>
   <Name>K</Name>
   <PassengerID>32</PassengerID>
   <PassengerFees>
    <SSRCode>VGMX</SSRCode>
   </PassengerFees>
  </BookingPassenger>
 </BookingPassengers>
</root>'

SELECT 
	p.c.value('(PassengerID/text())[1]', 'INT') AS PassengerID,
	f.c.value('(SSRCode/text())[1]', 'VARCHAR(10)') AS SSRCode
FROM @x.nodes( 'root/BookingPassengers/BookingPassenger' ) p(c)
	CROSS APPLY p.c.nodes('PassengerFees') f(c)

I have seen evidence that OPENXML is "faster" for larger XML documents but there are known memory issues with it, as per this post:

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

There are also know performance issues with using the parent axis (..), so you should try and avoid it if possible, by using multiple APPLY operators to drill into the XML, eg

Solving Parent Axis Access Performance Issues In XML Access In SQL Server 2005
http://blogs.technet.com/b/wardpond/archive/2006/02/19/database-programming-applying-apply-solving-parent-axis-access-performance-issues-in-xml-access-in-sql-server-2005.aspx