December 5, 2012 at 10:37 am
Hi,
DECLARE @t TABLE (cn VARCHAR(50), cq VARCHAR(50), vq VARCHAR(50), td VARCHAR(50), lo VARCHAR(50), im VARCHAR(50) )
INSERT INTO @t
SELECT 'wss-000','XX', 'WTZ', 'longdesc', 'RHRW-ABC', 'NA' UNION ALL
SELECT 'wss-001','RR', 'WTC', 'longdesc', 'RHRW-ABC', 'NA' UNION ALL
SELECT 'wss-001','LF', 'WTC', 'longdesc', 'LFW-ABC', 'NA' UNION ALL
SELECT 'wss-001','LF', 'WOTC', 'longdesc', 'LFW-ABC', 'NA' UNION ALL
SELECT 'wss-001','RR', 'WOTC', 'longdesc', 'RRW-ABC', 'NA' UNION ALL
SELECT 'wss-002','RR', 'WOXC', 'longdesc', 'RRW-ABQ', 'NA'
SELECT cn AS '@cn' ,
cq AS 'cq/@cq' ,
vq AS 'cq/vq/@vq' ,
td AS 'cq/vq/td/@td' ,
lo AS 'cq/vq/lo/@lo' ,
im AS 'cq/vq/im/@im'
FROM @t
FOR XML PATH('cn') , ROOT('root')
and I would like:
<root>
<cn cn="wss-000">
<cq cq="XX">
<vq vq="WTZ">
<td td="longdesc" />
<lo lo="RHRW-ABC" />
<im im="NA" />
</vq>
</cq>
</cn>
<cn cn="wss-001">
<cq cq="RR">
<vq vq="WTC">
<td td="longdesc" />
<lo lo="RHRW-ABC" />
<im im="NA" />
</vq>
<vq vq="WOTC">
<td td="longdesc" />
<lo lo="RRW-ABC" />
<im im="NA" />
</vq>
</cq>
<cq cq="LF">
<vq vq="WTC">
<td td="longdesc" />
<lo lo="LFW-ABC" />
<im im="NA" />
</vq>
<vq vq="WOTC">
<td td="longdesc" />
<lo lo="LFW-ABC" />
<im im="NA" />
</vq>
</cq>
</cn>
<cn cn="wss-002">
<cq cq="RR">
<vq vq="WOXC">
<td td="longdesc" />
<lo lo="RRW-ABQ" />
<im im="NA" />
</vq>
</cq>
</cn>
</root>
can anyone please help? thanks.
edit:typo
December 5, 2012 at 11:49 am
You're not doing yourself any favors by denormalizing the data. It makes the query reasonably tortured. Should look something like:
;with
t1 as (select distinct cn from @t),
t2 as (select distinct cn, cq from @t),
T3 as (select distinct cn, cq,vq from @t)
select
cn as '@cn',
(select cq as '@cq',
(select vq as '@vq',
(select td AS 'td/@td' ,
lo AS 'lo/@lo' ,
im AS 'im/@im'
FROM @t t4
where t4.cn=t3.cn and t4.cq=t3.cq and t4.vq=t3.vq
for XML path (''), type)
from T3
where t2.cn=t3.cn and t2.cq=t3.cq
for xml path ('vq'), type)
from t2 where t1.cn=t2.cn
for xml path ('cq'), type)
from t1
for XML path ('cn'), root('root')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 5, 2012 at 12:06 pm
See the following help topic in Books Online:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/7604161a-a958-446d-b102-7dee432979d0.htm
It might help you see how this works.. What you CANNOT do is just specifiy the structure via the selected values. It appears that one has to have the data records be structured that way to get such a result, using a nested query, as opposed to a single select.
Please post again with your results.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 11, 2012 at 6:40 am
Matt Miller (#4) (12/5/2012)
You're not doing yourself any favors by denormalizing the data. It makes the query reasonably tortured.
Hi Matt / Steve.
Thanks very much for your help. The requirements changes somewhat and I tried to do the the way you suggested... However I still cannot get this right. Can you please help?
Sample data:
CREATE TABLE #tbl(
[cn] [nvarchar](80) NULL,
[cq] [nvarchar](50) NULL,
[vq] [nvarchar](100) NULL,
[spn] [nvarchar](20) NULL,
[swc] [nvarchar](30) NULL
)
INSERT INTO #tbl ( cn, cq, vq, spn, swc )
SELECT 'cps',NULL,NULL,1,'GR' UNION ALL
SELECT 'cps',NULL,NULL,2,'DBO' UNION ALL
SELECT 'cpps',NULL,'MTA',1,NULL UNION ALL
SELECT 'cpps',NULL,'MTA',2,'GR' UNION ALL
SELECT 'cpps',NULL,'MTA',3,NULL UNION ALL
SELECT 'cpps',NULL,'MTA',4,'LBY' UNION ALL
SELECT 'cpps',NULL,'MTA',5,NULL UNION ALL
SELECT 'cop','n1',NULL,1,'LGW' UNION ALL
SELECT 'cop','n1',NULL,2,'R' UNION ALL
SELECT 'cop','n2',NULL,1,'PW' UNION ALL
SELECT 'cop','n2',NULL,2,'R' UNION ALL
SELECT 'cop','n3',NULL,1,'WP' UNION ALL
SELECT 'cop','n3',NULL,2,'R' UNION ALL
SELECT 'cop','n4',NULL,1,'DG' UNION ALL
SELECT 'cop','n4',NULL,2,'R'
And this is what I've done:
;
WITH t1
AS ( SELECT DISTINCT
cn ,
vq ,
cq
FROM #tbl
),
t2
AS ( SELECT DISTINCT
cn ,
vq ,
cq ,
spn ,
swc
FROM #tbl
)
SELECT cn AS 'Name' ,
( SELECT vq AS 'Name' ,
( SELECT cq AS 'Name' ,
( SELECT DISTINCT
[spn] AS 'PD/Number' ,
[swc] AS 'PD/SWC'
FROM t2
WHERE t2.cn = t1.cn
AND ISNULL(t2.vq,'') = ISNULL(t1.vq,'')
AND ISNULL(t2.cq,'') = ISNULL(t1.cq,'')
FOR XML PATH(''), ROOT('grp'), TYPE
)
FOR XML PATH(''), ROOT('cq') , TYPE
)
FOR XML PATH('') , ROOT('vq') , TYPE
)
FROM t1
ORDER BY cn ,
cq
FOR XML PATH('c') , ROOT('root')
And what I need is
<root>
<c>
<Name>cop</Name>
<vq>
<cq>
<Name>n1</Name>
<grp>
<PD>
<Number>1</Number>
<SWC>LGW</SWC>
</PD>
<PD>
<Number>2</Number>
<SWC>R</SWC>
</PD>
</grp>
<Name>n2</Name>
<grp>
<PD>
<Number>1</Number>
<SWC>PW</SWC>
</PD>
<PD>
<Number>2</Number>
<SWC>R</SWC>
</PD>
</grp>
<Name>n3</Name>
<grp>
<PD>
<Number>2</Number>
<SWC>R</SWC>
</PD>
<PD>
<Number>1</Number>
<SWC>WP</SWC>
</PD>
</grp>
<Name>n4</Name>
<grp>
<PD>
<Number>1</Number>
<SWC>DG</SWC>
</PD>
<PD>
<Number>2</Number>
<SWC>R</SWC>
</PD>
</grp>
</cq>
</vq>
</c>
<c>
<Name>cpps</Name>
<vq>
<Name>MTA</Name>
<cq>
<grp>
<PD>
<Number>1</Number>
</PD>
<PD>
<Number>3</Number>
</PD>
<PD>
<Number>5</Number>
</PD>
<PD>
<Number>2</Number>
<SWC>GR</SWC>
</PD>
<PD>
<Number>4</Number>
<SWC>LBY</SWC>
</PD>
</grp>
</cq>
</vq>
</c>
<c>
<Name>cps</Name>
<vq>
<cq>
<grp>
<PD>
<Number>2</Number>
<SWC>DBO</SWC>
</PD>
<PD>
<Number>1</Number>
<SWC>GR</SWC>
</PD>
</grp>
</cq>
</vq>
</c>
</root>
Thanks for your time.
December 11, 2012 at 11:08 am
Figured out. Thanks.
December 11, 2012 at 11:29 am
Each of the tiers needs to be based on the table contents in some way, since they might have attributes filled in.
Try adjust to something like:
;with
t1 as (select distinct cn from #tbl),
t2 as (select distinct cn, vq from #tbl),
t3 as (select distinct cn, vq,CQ from #tbl)
select cn as 'Name',
(
select vq as 'Name',
(
select cq as 'Name',
(
select spn as 'Number',
swc as 'SWC'
from #tbl t4
where t4.cn=t3.cn and
isnull(t4.vq,'')=isnull(t3.vq,'') and
isnull(t4.cq,'')=isnull(t3.cq,'')
for XML PATH('PD'), root('grp'), type
)
from t3 where t2.cn=t3.cn and isnull(t2.vq,'')=isnull(t3.vq,'')
for XML path('cq'),type
)
from t2
where t2.cn=t1.cn
for xml PATH('vq'), type
)
from t1
for XML path ('c'), root ('root')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply