CODEWITHSUNDEEP
sql-serverxmlsql-server-2008sql-server-2008-r2
tugberk
tugberk

Reputation: 58434

SQL Server from XML parameter to table - working with optional child nodes

On SQL Server 2008 R2, I am trying to read XML value as table.

So far, I am here : 

DECLARE @XMLValue AS XML;
SET @XMLValue = '<SearchQuery>
    <ResortID>1453</ResortID>
    <CheckInDate>2011-10-27</CheckInDate>
    <CheckOutDate>2011-11-04</CheckOutDate>
    <Room>
        <NumberOfADT>2</NumberOfADT>
        <CHD>
            <Age>10</Age>
        </CHD>
        <CHD>
            <Age>12</Age>
        </CHD>
    </Room>
    <Room>
        <NumberOfADT>1</NumberOfADT>
    </Room>
    <Room>
        <NumberOfADT>1</NumberOfADT>
        <CHD>
            <Age>7</Age>
        </CHD>
    </Room>
</SearchQuery>';

SELECT 
    Room.value('(NumberOfADT)[1]', 'INT') AS NumberOfADT
FROM @XMLValue.nodes('/SearchQuery/Room') AS SearchQuery(Room);

As you can see, Room node sometimes get CHD child nodes but sometimes don't.

Assume that I am getting this XML value as a Stored Procedure parameter. So, I need to work with the values in order to query my database tables. What would be the best way to read this XML parameter entirely?

EDIT

I think I need to express what I am expecting in return here. The below script code is for the table what I need here : 

DECLARE @table AS TABLE(
    ResorrtID INT,
    CheckInDate DATE,
    CheckOutDate DATE,
    NumberOfADT INT,
    CHDCount INT,
    CHDAges NVARCHAR(100)
);

For the XML value I have provide above, the below Insert t-sql is suitable : 

INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 2, 2, '10;12');
INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 0, NULL);
INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 1, '7');

CHDCount is for the number of CHD nodes under Room node. Also, how many Room node I have, that many table row I am having here.

As for how it should look, see the below picture :

enter image description here

Actually, this code is for hotel reservation search query. So, I need to work with these values I got from XML parameter to query my tables and return available rooms. I am telling this because maybe it helps you guys to see it through. I am not looking for a complete code for room reservation system. That would be so selfish.

Upvotes: 3

Views: 2540

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

This should get you close:

SELECT  ResortID = @xmlvalue.value('(//ResortID)[1]', 'int')
      , CheckInDate = @xmlvalue.value('(//CheckInDate)[1]', 'date')
      , CheckOutDate = @xmlvalue.value('(//CheckOutDate)[1]', 'date')
      , NumberOfAdt = Room.value('(NumberOfADT)[1]', 'INT')
      , CHDCount = Room.value('count(./CHD)', 'int')
      , CHDAges = Room.query('for $c in ./CHD
                        return concat(($c/Age)[1], ";")').value('(.)[1]',
                                                              'varchar(100)')
FROM    @XMLValue.nodes('/SearchQuery/Room') AS SearchQuery ( Room ) ;

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select S.X.value('ResortID[1]', 'int') as ResortID,
       S.X.value('CheckInDate[1]', 'date') as CheckInDate,
       S.X.value('CheckOutDate[1]', 'date') as CheckOutDate,
       R.X.value('NumberOfADT[1]', 'int') as NumberOfADT,
       R.X.value('count(CHD)', 'int') as CHDCount,
       stuff((select ';'+C.X.value('.', 'varchar(3)')
              from R.X.nodes('CHD/Age') as C(X)
              for xml path('')), 1, 1, '') as CHDAges
from @XMLValue.nodes('/SearchQuery') as S(X)
  cross apply S.X.nodes('Room') as R(X)

Upvotes: 2

Related Questions