SQL Query CASE statement in SELECT

I have two problems and I believe it has to do with my query in the CASE statement. First one is how to get the parname value to show up in my parname without having to add it in SELECT, because if I add it in SELECT as a column, my xml output is messed up. Second is how I can tweak my CASE such that the group GID element is present only when parname = setgroup.

My table:
MODNM | CARNM | PARTID   | PARNAME  | SEQ | TYPE | VALUE                     | FLAG
-----------------------------------------------------------------------------------
M-880 | HONA  | 29000153 | SETGROUP | 1   | A    | 3                         | P
M-880 | HONA  | 29000153 | *CDMZI   | 2   | A    | F%+A8E=,,,0;+MS=V123,456; | P
M-880 | HONA  | 29000153 | DMTZ     | 3   | A    |                           | P
M-880 | HONA  | 29000153 | CDRFCP   | 4   | A    | 0                         | P
M-880 | HONA  | 29000153 | SETGROUP | 5   | A    | 15                        | P
M-880 | HONA  | 29000153 | #PT      | 5   | 6    | NONE                      | P
M-880 | HONA  | 29000153 | CDH6     | 7   | A    | 100.111.100.44            | P
M-880 | HONA  | 29000153 | CDEPORT  | 8   | A    | 9003                      | P

Open in new window


Desired XML Output Format:
<Device PartID="29000153" MODNM="M-880">
  <Car Name="HONA">
    <Group GID="3">
      <Pmeter ParName="*CDMZI" Value="F%+A8E=,,,0;+MS=V123,456;" Type="A" Flag="P" />
      <Pmeter ParName="DMTZ" Value="" Type="A" Flag="P" />
      <Pmeter ParName="CDRFCP" Value="0" Type="A" Flag="P" />
    </Group>
    <Group GID="15">
      <Pmeter ParName="#PT" Value="NONE" Type="A" Flag="P" />
      <Pmeter ParName="CDH6" Value="100.111.100.44" Type="A" Flag="P" />
      <Pmeter ParName="CDEPORT" Value="9003" Type="A" Flag="P" />
    </Group>
  </Car>
  <DevFiles />
</Device>

Open in new window


Actual Output Format:
<Device PartID="29000153" MODNM="M-880">
  <Car Name="HONA">
    <Group GID="3">
	  <Pmeter ParName="0" Value="3" Type="A" Flag="P" />
	</Group>
    <Group GID="F%+A8E=,,,0;+MS=V123,456;">	
      <Pmeter ParName="1" Value="F%+A8E=,,,0;+MS=V123,456;" Type="A" Flag="P" />
      <Pmeter ParName="1" Value="" Type="A" Flag="P" />
      <Pmeter ParName="1" Value="0" Type="A" Flag="P" />
    </Group>
    <Group GID="15">
	  <Pmeter ParName="0" Value="15" Type="A" Flag="P" />	
    </Group>	
    <Group GID="NONE">	
      <Pmeter ParName="1" Value="NONE" Type="A" Flag="P" />
      <Pmeter ParName="1" Value="100.111.100.44" Type="A" Flag="P" />
      <Pmeter ParName="1" Value="9003" Type="A" Flag="P" />
    </Group>
  </Car>
  <DevFiles />
</Device>

Open in new window


Query:
<cfquery name="getParts" ....>
    SELECT PARTID
			, MODNUM
            , CARNM
            , CASE WHEN PARNAME = 'SETGROUP' THEN 0 ELSE 1 END AS PARTGroup
			, VALUE
			, TYPE
			, FLAG
    FROM   PMETER 
    WHERE  PARTID IN ('29000153')
    ORDER BY PARTID
            , CARNM
            , SEQ
            , PARTGroup
</cfquery>

Open in new window


Generate XML:
<cfprocessingdirective suppresswhitespace="Yes">
<cfcontent type="text/xml; charset=utf-8">
<cfxml variable="xmlobject">
<cfoutput query="getParts" group="PARTID">
<Device PartID="#PARTID#" MODNM="#MODNUM#">
   <cfoutput group="CARNM">
      <Car Name="#CARNM#">
      <cfoutput group="PARTGroup">  
         <Group GID="#VALUE#">
         <cfoutput>
         <Pmeter ParName="#PARNAME#" Value="#VALUE#" Type="#TYPE#" Flag="#FLAG#" />
         </cfoutput>
         </Group>
     </cfoutput>
     </Car>
   </cfoutput>
</Device>
</cfoutput>
</cfxml>

<cfoutput>#xmlobject#</cfoutput>
</cfprocessingdirective>

Open in new window

bman2011Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Why are you not doing it in T-SQL?

DECLARE @Sample TABLE
    (
      MODNM NVARCHAR(255) ,
      CARNM NVARCHAR(255) ,
      PARTID INT ,
      PARNAME NVARCHAR(255) ,
      SEQ INT ,
      [TYPE] NVARCHAR(255) ,
      VALUE NVARCHAR(255) ,
      FLAGN VARCHAR(255)
    );


INSERT  INTO @Sample
VALUES  ( 'M-880', 'HONA', 29000153, 'SETGROUP', 1, 'A', '3', 'P' ),
        ( 'M-880', 'HONA', 29000153, '*CDMZI', 2, 'A', 'F%+A8E=,,,0;+MS=V123,456;', 'P' ),
        ( 'M-880', 'HONA', 29000153, 'DMTZ', 3, 'A', NULL, 'P' ),
        ( 'M-880', 'HONA', 29000153, 'CDRFCP', 4, 'A', '0', 'P' ),
        ( 'M-880', 'HONA', 29000153, 'SETGROUP', 5, 'A', '15', 'P' ),
        ( 'M-880', 'HONA', 29000153, '#PT', 5, '6', 'NONE', 'P' ),
        ( 'M-880', 'HONA', 29000153, 'CDH6', 7, 'A', '100.111.100.44', 'P' ),
        ( 'M-880', 'HONA', 29000153, 'CDEPORT', 8, 'A', '9003', 'P' );

WITH    Data
          AS ( SELECT   O.MODNM ,
                        O.CARNM ,
                        O.PARTID ,
                        O.PARNAME ,
                        O.SEQ ,
                        O.TYPE ,
                        O.VALUE ,
                        O.FLAGN ,
                        ( SELECT TOP 1
                                    CAST([VALUE] AS INT) 
                          FROM      @Sample I
                          WHERE     I.PARTID = O.PARTID
                                    AND I.MODNM = O.MODNM
                                    AND I.CARNM = O.CARNM
                                    AND I.PARNAME = 'SETGROUP'
                                    AND I.SEQ <= O.SEQ
                          ORDER BY  I.SEQ DESC
                        ) AS GID
               FROM     @Sample O
               WHERE    O.PARNAME <> 'SETGROUP'
             )
    SELECT  S1.PARTID AS [@PartID] ,
            S1.MODNM AS [@MODNM] ,
            S1.CARNM AS [Car/@Name] ,
            ( SELECT    S2.GID AS [@GID] ,
                        ( SELECT    S3.PARNAME AS [@ParName] ,
                                    S3.VALUE AS [@Value] ,
                                    S3.[TYPE] AS [@Type] ,
                                    S3.FLAGN AS [@Flag]
                          FROM      Data S3
                          WHERE     S3.PARTID = S2.PARTID
                                    AND S3.MODNM = S2.MODNM
                                    AND S3.CARNM = S2.CARNM
                                    AND S3.GID = S2.GID
                          ORDER BY  S3.SEQ
						  FOR		XML PATH('Pmeter') , TYPE
                        )
              FROM      Data S2
              WHERE     S2.PARTID = S1.PARTID
                        AND S2.MODNM = S1.MODNM
                        AND S2.CARNM = S1.CARNM
              GROUP BY  S2.PARTID ,
                        S2.MODNM ,
                        S2.CARNM ,
                        S2.GID              
              FOR		XML PATH('Group') , TYPE
            ) AS [Car]
    FROM    Data S1
    GROUP BY S1.PARTID ,
            S1.MODNM ,
            S1.CARNM
	FOR     XML PATH('Device');

Open in new window


Result is

<Device PartID="29000153" MODNM="M-880">
  <Car Name="HONA">
    <Group GID="3">
      <Pmeter ParName="*CDMZI" Value="F%+A8E=,,,0;+MS=V123,456;" Type="A" Flag="P" />
      <Pmeter ParName="DMTZ" Type="A" Flag="P" />
      <Pmeter ParName="CDRFCP" Value="0" Type="A" Flag="P" />
    </Group>
    <Group GID="15">
      <Pmeter ParName="#PT" Value="NONE" Type="6" Flag="P" />
      <Pmeter ParName="CDH6" Value="100.111.100.44" Type="A" Flag="P" />
      <Pmeter ParName="CDEPORT" Value="9003" Type="A" Flag="P" />
    </Group>
  </Car>
</Device>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.