|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
May 29th, 2008, 05:35 AM
|
|||
|
|||
|
Challenge for SQL Query Experts...
Challenge for SQL Query Experts...
I Created one table like... create table Reports(ID numeric(5),[Desc] nvarchar(50),Parameters xml) and I inserted 2 Records like... insert into Reports(1,'customer',' <Report Name="Report Name"> <Parameters> <Parameter ID="1" Name="ID"/> <Parameter ID="2" Name="NAME"/> <Parameter ID="3" Name="DOB"/> <Parameter ID="4" Name="AGE"/> </Parameters> </Report>') insert into Reports(2,'bank',' <Report Name="Report Name"> <Parameters> <Parameter ID="1" Name="ID"/> <Parameter ID="2" Name="NAME"/> <Parameter ID="3" Name="ACCOUNT"/> <Parameter ID="4" Name="BALANCE"/> </Parameters> </Report>') Now My Question is How to get the distinct parameters in the 'Reports' Table like...... ID NAME DOB AGE ACCOUNT BALANCE What is the SQL Query ????????????? Thanks in Advance :-) Prakash.C
|
|
#2
May 29th, 2008, 09:26 AM
|
||||||
|
||||||
|
I haven't used sql server and xml, but there is a good article on MSDN on using it.
http://msdn.microsoft.com/en-us/library/ms345117.aspx Hope this helps.
|
|
#3
July 14th, 2008, 08:51 AM
|
|||
|
|||
|
Code:
select distinct c.value('@Name', 'varchar(100)') as name
from reports cross apply Parameters.nodes('//Parameter') T(c)
Or with some semi-intelligent ordering... Code:
; with a as (
select c.value('@Name', 'varchar(100)') as name, c.value('@ID', 'int') as id
from reports cross apply Parameters.nodes('//Parameter') T(c))
select name from a group by name order by avg(1.0 * id)
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Challenge for SQL Query Experts... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
