X Query Pls Help

[gundugadu]

please help here is the xml data i am dealing with.

<rates>
<rate rateid="5040" description="National Forest" total="30">
<ratefee ratefeeid="31166" description="Daily" fee="5.00" count="5" total="25.00" />
<adhoc adhocfeeid="1" description="TEST Rate1" fee="2.5" count="2" total="5.00" />
</rate>
</rates>


DECLARE @AuditRateXML XML='<rates>
<rate rateid="5040" description="National Forest" total="30">
<ratefee ratefeeid="31166" description="Daily" fee="5.00" count="5" total="25.00"/>
<adhoc adhocfeeid="1" description="TEST Rate1" fee="2.5" count="2" total="5.00"/>
</rate>
</rates>'

I want to extract data from the xml variable.

--required ouput

[b]count description fee ratedescr adhocfeeid ratefeeid rateid[/b]
2 TEST Rate1 2.50 National Forest 1 NULL 5040
5 Daily 5.00 National Forest 0 31166 5040

[cherlapalli_jailer]

[ChampakDas]

I wish I could help...LTT

[cherlapalli_jailer]

u want to unmarshall XML or

u want to pull data from DB in XML format ??
can u pl elaborate?

[loveindia]

Sql Server lo aitey I can help. Oracle aitey cannot....

[gundugadu]

[quote name='loveindia' timestamp='1381412515' post='1304387465']
Sql Server lo aitey I can help. Oracle aitey cannot....
[/quote]
sql lone vayya

[loveindia]

Here you go...

[code]

DECLARE @AuditRateXML XML='<rates>
<rate rateid="5040" description="National Forest" total="30">
<ratefee ratefeeid="31166" description="Daily" fee="5.00" count="5" total="25.00"/>
<adhoc adhocfeeid="1" description="TEST Rate1" fee="2.5" count="2" total="5.00"/>
</rate>
</rates>'

SELECT prod.c.value('@count', 'INT') as [Count],
prod.c.value('@description', 'VARCHAR(100)') as [Description],
prod.c.value('@fee', 'DECIMAL(5,2)') as Fee,
prod.c.value('../@description', 'VARCHAR(100)') as [RateDescr],
0 as Adhocfeeid,
prod.c.value('@ratefeeid', 'INT') as [Ratefeeid],
prod.c.value('../@rateid', 'INT') as Rateid
FROM @AuditRateXML.nodes('/rates/rate/ratefee') AS prod(c)
UNION
SELECT prod.c.value('@count', 'INT') as [Count],
prod.c.value('@description', 'VARCHAR(100)') as [Description],
prod.c.value('@fee', 'DECIMAL(5,2)') as Fee,
prod.c.value('../@description', 'VARCHAR(100)') as [RateDescr],
prod.c.value('@adhocfeeid', 'INT') as Adhocfeeid,
prod.c.value('@ratefeeid', 'INT') as [Ratefeeid],
prod.c.value('../@rateid', 'INT') as Rateid
FROM @AuditRateXML.nodes('/rates/rate/adhoc') AS prod(c)

[/code]

[gundugadu]

[quote name='loveindia' timestamp='1381416678' post='1304387880']
Here you go...

[code]

DECLARE @AuditRateXML XML='<rates>
<rate rateid="5040" description="National Forest" total="30">
<ratefee ratefeeid="31166" description="Daily" fee="5.00" count="5" total="25.00"/>
<adhoc adhocfeeid="1" description="TEST Rate1" fee="2.5" count="2" total="5.00"/>
</rate>
</rates>'

SELECT prod.c.value('@count', 'INT') as [Count],
prod.c.value('@description', 'VARCHAR(100)') as [Description],
prod.c.value('@fee', 'DECIMAL(5,2)') as Fee,
prod.c.value('../@description', 'VARCHAR(100)') as [RateDescr],
0 as Adhocfeeid,
prod.c.value('@ratefeeid', 'INT') as [Ratefeeid],
prod.c.value('../@rateid', 'INT') as Rateid
FROM @AuditRateXML.nodes('/rates/rate/ratefee') AS prod(c)
UNION
SELECT prod.c.value('@count', 'INT') as [Count],
prod.c.value('@description', 'VARCHAR(100)') as [Description],
prod.c.value('@fee', 'DECIMAL(5,2)') as Fee,
prod.c.value('../@description', 'VARCHAR(100)') as [RateDescr],
prod.c.value('@adhocfeeid', 'INT') as Adhocfeeid,
prod.c.value('@ratefeeid', 'INT') as [Ratefeeid],
prod.c.value('../@rateid', 'INT') as Rateid
FROM @AuditRateXML.nodes('/rates/rate/adhoc') AS prod(c)

[/code]
[/quote]

VAYYA u rock reeee
thank you so much vayya



neeeku yedina manchi tutorials thelisthe post cheya vayya want to preactice this X query in SQL

once again thanks for ur help vayya