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

Question

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.

@marc_s thanks for the touch on the title. I am not sure that it a good title here. If you can provide a better title, feel free to change it pls. — tugberk
– tugberk, Commented Oct 25, 2011 at 11:40
What do you want your destination table of parameters to look like? Do you care about the child information, or jsut the number of ADT per room? is NumberofADT the count of child rows? — Stuart Ainsworth
– Stuart Ainsworth, Commented Oct 25, 2011 at 12:03
Could you give a clearer description of the outputs required - which values do you need and how do you need to use them? — Ed Harper
– Ed Harper, Commented Oct 25, 2011 at 12:04

Mikael Eriksson · Accepted Answer · 2011-10-25 12:42:04Z

2

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)

answered Oct 25, 2011 at 12:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tugberk Over a year ago

do you have any idea how awesome guy you are? Thanks a lot!

Stuart Ainsworth · Accepted Answer · 2011-10-25 12:50:23Z

1

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 ) ;

answered Oct 25, 2011 at 12:50

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

2 Comments

Stuart Ainsworth Over a year ago

d'oh; missed the page refresh :)

tugberk Over a year ago

thanks! I already got it as you see but thanks for the effort :)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related