none
XML problem RRS feed

  • 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
    Tuesday, February 15, 2011 7:27 AM

Answers

  •  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
    • Edited by Jon Gurgul Tuesday, February 15, 2011 8:34 AM tidy, syntax
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 8:35 AM
    Tuesday, February 15, 2011 8:25 AM
  • SELECT	n.value('../PassengerID[1]', 'INT') AS PassengerID,
    	n.value('SSRCode[1]', 'VARCHAR(10)') AS SSRCode
    FROM	@x.nodes('/root/BookingPassengers/BookingPassenger/PassengerFees') AS pf(n)
    
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 8:35 AM
    Tuesday, February 15, 2011 8:30 AM
  • 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
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 8:35 AM
    Tuesday, February 15, 2011 8:31 AM
  • ../../
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 9:12 AM
    Tuesday, February 15, 2011 9:07 AM
  • 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

     

    • Proposed as answer by Naomi NModerator Tuesday, February 15, 2011 11:55 PM
    • Marked as answer by Rishabh K Wednesday, February 16, 2011 3:41 AM
    Tuesday, February 15, 2011 9:13 AM

All replies

  • 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/
    Tuesday, February 15, 2011 8:25 AM
  •  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
    • Edited by Jon Gurgul Tuesday, February 15, 2011 8:34 AM tidy, syntax
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 8:35 AM
    Tuesday, February 15, 2011 8:25 AM
  • 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
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 8:35 AM
    Tuesday, February 15, 2011 8:31 AM
  • 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/
    Tuesday, February 15, 2011 8:33 AM
  • 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

    Tuesday, February 15, 2011 8:35 AM
  • Hi,

    Thanks ALL,

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


    Thanks and regards, Rishabh
    Tuesday, February 15, 2011 8:39 AM
  • Hi,

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

    is there any thing like 3 dots


    Thanks and regards, Rishabh
    Tuesday, February 15, 2011 8:51 AM
  • ../../
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    • Marked as answer by Rishabh K Tuesday, February 15, 2011 9:12 AM
    Tuesday, February 15, 2011 9:07 AM
  • 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

     

    • Proposed as answer by Naomi NModerator Tuesday, February 15, 2011 11:55 PM
    • Marked as answer by Rishabh K Wednesday, February 16, 2011 3:41 AM
    Tuesday, February 15, 2011 9:13 AM