Question

2008年4月30日 R2,我试图将XML数值作为表格。

到目前为止,我在这里是:

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

如您所知,Room。有时,儿童节点,但有时没有。

假定我正在将XML作为储存程序参数。因此,我需要与这些数值合作,以便查询我的数据库表。什么是完全读一下这支十ML的参数?

http://www.ohchr.org。

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.

关于它应如何研究,见以下图景:

“entergraph

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.

Answer 1

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)

Answer 2

这应当使你结束:

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

友情链接