Need help to read XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to read XML

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-30 : 12:14:22

I've as follow,

declare @data xml
set @data='<?xml version="1.0" standalone="yes"?>
<!--Created with the XmlDocument class.-->
<MyStay>
  <chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
    <StayRM ID="1" rmNo="D -[2]" rmType="DELUXE" rmPosi="col2" rmPric="50.30" />
  </chkIN>
  <chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
    <StayRM ID="1" rmNo="D -[3]" rmType="DELUXE" rmPosi="col3" rmPric="50.30" />
  </chkIN>
  <chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
    <StayRM ID="1" rmNo="D -[4]" rmType="DELUXE" rmPosi="col4" rmPric="50.30" />
  </chkIN>
</MyStay>'

insert into @t1(chkID, sDt, eDt, roomNo, roomType, roomPosi, price)
/*need help to built SQL to read @data here ....*/

The final results as follow,

idx   | chkID   | sDt   | eDt   | roomNo   | roomType  | roomPosi  | price
----------------------------------------------------------------------------------
1	1	2010-05-01 00:00:00.000	2010-05-10 00:00:00.000	D -[2]	DELUXE	col2	50.30
2	1	2010-05-01 00:00:00.000	2010-05-10 00:00:00.000	D -[3]	DELUXE	col3	50.30
3	1	2010-05-01 00:00:00.000	2010-05-10 00:00:00.000	D -[4]	DELUXE	col4	50.30

Looking for help

Sachin.Nand

2937 Posts

Posted - 2010-04-30 : 14:32:25

[code]
declare @x xml='<?xml version="1.0" standalone="yes"?>

<MyStay>
<chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
<StayRM ID="1" rmNo="D -[2]" rmType="DELUXE" rmPosi="col2" rmPric="50.30" />
</chkIN>
<chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
<StayRM ID="1" rmNo="D -[3]" rmType="DELUXE" rmPosi="col3" rmPric="50.30" />
</chkIN>
<chkIN ID="1" sDt="2010-05-01" eDt="2010-05-10">
<StayRM ID="1" rmNo="D -[4]" rmType="DELUXE" rmPosi="col4" rmPric="50.30" />
</chkIN>
</MyStay>'

select @x

select T1.ChkId,T1.edt,T1.sdt,T.price,T.roomNo,T.roomPosi,T.roomType from
(select distinct x.i.value('@ID','varchar(50)')as ChkId ,
x.i.value('@sDt','varchar(50)')as sdt,
x.i.value('@eDt','varchar(50)')as edt
from @x.nodes('/MyStay/chkIN')x(i))T1 inner join (select x.i.value('@ID','varchar(50)')as ChkId,x.i.value('@rmNo','varchar(50)')as roomNo,
x.i.value('@rmType','varchar(50)')as roomType,x.i.value('@rmPosi','varchar(50)')as roomPosi,x.i.value('@rmPric','varchar(50)')as price
from @x.nodes('/MyStay/chkIN/StayRM')x(i))T on T.ChkId=T1.ChkId

[/code]

PBUH

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-30 : 21:51:54

tq sir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-01 : 00:22:25

Somewhat simpler...

select	ROW_NUMBER() over (order by n) as idx,
	n.value('@ID', 'int') as chkID,
	n.value('../@sDt', 'datetime') as sDt,
	n.value('../@eDt', 'datetime') as eDt,
	n.value('@rmNo', 'varchar(max)') as roomNo,
	n.value('@rmType', 'varchar(max)') as roomType,
	n.value('@rmPosi', 'varchar(max)') as roomPosi,
	n.value('@rmPric', 'smallmoney') as price
from	@x.nodes('/MyStay/chkIN/StayRM') as c(n)

N 56°04'39.26"
E 12°55'05.63"

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-05-01 : 06:16:49

tq very much mr. peso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources