markyb0y markyb0y - 1 month ago 7
SQL Question

SQL XML - Cannot group multiple columns

I have a query that returns Server Details that are held in a varchar(max) xml field in SQL
My issue is with the drive columns, a Server can have multiple drives but these are multiple child items under the server parent node in XML, I have used the CASE WHEN lines to separate the drives into columns, but each drive letter generates its own row, so a Server with 2 drives creates 2 rows

STName STDescription RTName RTDescription RTQty RTCode NTName NTDescription NTPVLAN NTSVLAN NTDomain NTRam NTCpus NTVMTemplate NTSCOM RTCode Cdrive Ddrive Edrive Fdrive Gdrive
Basic Service Single Server Service Standard Server Basic server configuration 1 STD Standard Server Basic server configuration {{int}} NULL {{primary}} 4 2 1NIC2012R2CLUST TRUE STD 50 NULL NULL NULL NULL
Basic Service Single Server Service Standard Server Basic server configuration 1 STD Standard Server Basic server configuration {{int}} NULL {{primary}} 4 2 1NIC2012R2CLUST TRUE STD NULL 20 NULL NULL NULL


I hope its a fairly simple job to adjust my code to place both drives on the same row?

STName STDescription RTName RTDescription RTQty RTCode NTName NTDescription NTPVLAN NTSVLAN NTDomain NTRam NTCpus NTVMTemplate NTSCOM RTCode Cdrive Ddrive Edrive Fdrive Gdrive
Basic Service Single Server Service Standard Server Basic server configuration 1 STD Standard Server Basic server configuration {{int}} NULL {{primary}} 4 2 1NIC2012R2CLUST TRUE STD 50 20 NULL NULL NULL


its driving me mad :S

DECLARE @XML xml; SET @XML = (SELECT CONVERT(XML, CONVERT(NVARCHAR(max), TemplateXml )) FROM dbTemplates FOR XML AUTO, Root('ACME'))
SELECT
ST.C.value('(Name/text())[1]', 'nvarchar(max)') as STName,
ST.C.value('(Description/text())[1]', 'nvarchar(max)') as STDescription,
RT.C.value('(Name/text())[1]', 'nvarchar(max)') as RTName,
RT.C.value('(Description/text())[1]', 'nvarchar(max)') as RTDescription,
RT.C.value('(Quantity/text())[1]', 'nvarchar(max)') as RTQty,
RT.C.value('(Code/text())[1]', 'nvarchar(max)') as RTCode,
NT.C.value('(Name/text())[1]', 'nvarchar(max)') as NTName,
NT.C.value('(Description/text())[1]', 'nvarchar(max)') as NTDescription,
NT.C.value('(PrimaryVlan/text())[1]', 'nvarchar(max)') as NTPVLAN,
NT.C.value('(SecondaryVlan/text())[1]', 'nvarchar(max)') as NTSVLAN,
NT.C.value('(Domain/text())[1]', 'nvarchar(max)') as NTDomain,
NT.C.value('(RamSize/text())[1]', 'nvarchar(max)') as NTRam,
NT.C.value('(CpuCores/text())[1]', 'nvarchar(max)') as NTCpus,
NT.C.value('(VmTemplate/text())[1]', 'nvarchar(max)') as NTVMTemplate,
NT.C.value('(ScomInstall/text())[1]', 'nvarchar(max)') as NTSCOM,
NT.C.value('(SccmInstall/text())[1]', 'nvarchar(max)') as NTSCCM,
CASE WHEN DP.C.value('(Letter/text())[1]', 'nvarchar(max)') = 'C' THEN DP.C.value('(Capacity/text())[1]', 'nvarchar(max)') END AS Cdrive,
CASE WHEN DP.C.value('(Letter/text())[1]', 'nvarchar(max)') = 'D' THEN DP.C.value('(Capacity/text())[1]', 'nvarchar(max)') END AS Ddrive,
CASE WHEN DP.C.value('(Letter/text())[1]', 'nvarchar(max)') = 'E' THEN DP.C.value('(Capacity/text())[1]', 'nvarchar(max)') END AS Edrive,
CASE WHEN DP.C.value('(Letter/text())[1]', 'nvarchar(max)') = 'F' THEN DP.C.value('(Capacity/text())[1]', 'nvarchar(max)') END AS Fdrive,
CASE WHEN DP.C.value('(Letter/text())[1]', 'nvarchar(max)') = 'G' THEN DP.C.value('(Capacity/text())[1]', 'nvarchar(max)') END AS Gdrive
from @XML.nodes('ACME/dbTemplates/ServiceTemplate') as ST(C)
outer apply ST.C.nodes('Roles') as RO(C)
outer apply RO.C.nodes('RoleTemplate') as RT(C)
outer apply RT.C.nodes('NodeTemplate') as NT(C)
outer apply NT.C.nodes('Drives') as DR(C)
outer apply DR.C.nodes('DriveParameter') as DP(C)
ORDER BY STName,RTName


here is a section of the XML as requested

<ACME>
<dbTemplates>
<ServiceTemplate xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<TemplateId>3205ac97-6396-4acb-8f93-5704297f1bbc</TemplateId>
<Name>Basic Service</Name>
<Description>Single Server Service</Description>
<Roles>
<RoleTemplate>
<TemplateId xsi:nil="true" />
<Name>Standard Server</Name>
<Description>Basic server configuration</Description>
<Quantity>1</Quantity>
<Code>STD</Code>
<NodeTemplate>
<TemplateId xsi:nil="true" />
<Name>Standard Server</Name>
<Description>Basic server configuration</Description>
<PrimaryVlan>{{int}}</PrimaryVlan>
<Domain>{{primary}}</Domain>
<RamSize>4</RamSize>
<CpuCores>2</CpuCores>
<Drives>
<DriveParameter>
<Letter>C</Letter>
<Capacity>50</Capacity>
</DriveParameter>
<DriveParameter>
<Letter>D</Letter>
<Capacity>20</Capacity>
</DriveParameter>
<DriveParameter>
<Letter>E</Letter>
<Capacity>50</Capacity>
</DriveParameter>
</Drives>
<VmTemplate>1NIC2012R2CLUST</VmTemplate>
<ScomInstall>True</ScomInstall>
<SccmInstall>True</SccmInstall>
</NodeTemplate>
</RoleTemplate>
</Roles>
</ServiceTemplate>
</dbTemplates>
</ACME>

Answer

By using nodes() on <DriveParameter> you get all of them row-wise. The advantage was, that you can deal with any number.

What you want is a side-by-side result. In this case you must know, which letters you expect and use them as XQuery-filter (and be aware, that XQuery is case sensitive. The following solution will work with capitalized letters only...

And one hint: It is simpler to read, if you call your CROSS APPLYs like A(ST), B(RO), C(RT) and so on. Then you can call ST.value(...) and avoid the repeated .C.value of your query...

DECLARE @xml XML=
'<ACME>
  <dbTemplates>
    <ServiceTemplate xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <TemplateId>3205ac97-6396-4acb-8f93-5704297f1bbc</TemplateId>
      <Name>Basic Service</Name>
      <Description>Single Server Service</Description>
      <Roles>
        <RoleTemplate>
          <TemplateId xsi:nil="true" />
          <Name>Standard Server</Name>
          <Description>Basic server configuration</Description>
          <Quantity>1</Quantity>
          <Code>STD</Code>
          <NodeTemplate>
            <TemplateId xsi:nil="true" />
            <Name>Standard Server</Name>
            <Description>Basic server configuration</Description>
            <PrimaryVlan>{{int}}</PrimaryVlan>
            <Domain>{{primary}}</Domain>
            <RamSize>4</RamSize>
            <CpuCores>2</CpuCores>
            <Drives>
              <DriveParameter>
                <Letter>C</Letter>
                <Capacity>50</Capacity>
              </DriveParameter>
              <DriveParameter>
                <Letter>D</Letter>
                <Capacity>20</Capacity>
              </DriveParameter>
              <DriveParameter>
                <Letter>E</Letter>
                <Capacity>50</Capacity>
              </DriveParameter>
            </Drives>
            <VmTemplate>1NIC2012R2CLUST</VmTemplate>
            <ScomInstall>True</ScomInstall>
            <SccmInstall>True</SccmInstall>
          </NodeTemplate>
        </RoleTemplate>
      </Roles>
    </ServiceTemplate>
  </dbTemplates>
</ACME>';

--The query

SELECT
ST.C.value('(Name/text())[1]', 'nvarchar(max)') as STName,
ST.C.value('(Description/text())[1]', 'nvarchar(max)') as STDescription,
RT.C.value('(Name/text())[1]', 'nvarchar(max)') as RTName,
RT.C.value('(Description/text())[1]', 'nvarchar(max)') as RTDescription,
RT.C.value('(Quantity/text())[1]', 'nvarchar(max)') as RTQty,
RT.C.value('(Code/text())[1]', 'nvarchar(max)') as RTCode,
NT.C.value('(Name/text())[1]', 'nvarchar(max)') as NTName,
NT.C.value('(Description/text())[1]', 'nvarchar(max)') as NTDescription,
NT.C.value('(PrimaryVlan/text())[1]', 'nvarchar(max)') as NTPVLAN,
NT.C.value('(SecondaryVlan/text())[1]', 'nvarchar(max)') as NTSVLAN,
NT.C.value('(Domain/text())[1]', 'nvarchar(max)') as NTDomain,
NT.C.value('(RamSize/text())[1]', 'nvarchar(max)') as NTRam,
NT.C.value('(CpuCores/text())[1]', 'nvarchar(max)') as NTCpus,
NT.C.value('(VmTemplate/text())[1]', 'nvarchar(max)') as NTVMTemplate,
NT.C.value('(ScomInstall/text())[1]', 'nvarchar(max)') as NTSCOM,
NT.C.value('(SccmInstall/text())[1]', 'nvarchar(max)') as NTSCCM,

NT.C.value('(Drives/DriveParameter[Letter="C"]/Capacity)[1]','int') AS CDrive,
NT.C.value('(Drives/DriveParameter[Letter="D"]/Capacity)[1]','int') AS DDrive,
NT.C.value('(Drives/DriveParameter[Letter="E"]/Capacity)[1]','int') AS EDrive,
NT.C.value('(Drives/DriveParameter[Letter="F"]/Capacity)[1]','int') AS FDrive,
NT.C.value('(Drives/DriveParameter[Letter="G"]/Capacity)[1]','int') AS GDrive

from @XML.nodes('ACME/dbTemplates/ServiceTemplate') as ST(C)
outer apply ST.C.nodes('Roles') as RO(C)
outer apply RO.C.nodes('RoleTemplate') as RT(C)
outer apply RT.C.nodes('NodeTemplate') as NT(C)
ORDER BY STName,RTName
Comments