XML format issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » XML format issues

XML format issues

Rate Topic

Display Mode

Topic Options

Author

Message

clayman

Posted Wednesday, December 05, 2012 10:37 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:47 PM
Points: 237, Visits: 1,215

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

Post #1393123

Matt Miller (#4)

Posted Wednesday, December 05, 2012 11:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

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?

Post #1393151

sgmunson

Posted Wednesday, December 05, 2012 12:06 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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

Weight Loss Tips

Post #1393158

clayman

Posted Tuesday, December 11, 2012 6:40 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:47 PM
Points: 237, Visits: 1,215

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.

Post #1395086

clayman

Posted Tuesday, December 11, 2012 11:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:47 PM
Points: 237, Visits: 1,215

Figured out. Thanks.

Post #1395251

Matt Miller (#4)

Posted Tuesday, December 11, 2012 11:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

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

Post #1395258

« Prev Topic | Next Topic »

Permissions