Не удается сгруппировать несколько столбцов из XML

У меня есть запрос, который возвращает сведения о сервере, которые хранятся в поле xml varchar(max) в SQL
Моя проблема заключается в Столбцах диска, сервер может иметь несколько дисков, но это несколько дочерних элементов под родительским узлом сервера в XML, я использовал случай, когда строки для разделения дисков на столбцы, но каждая буква диска создает свою собственную строку, поэтому сервер с 2 дисками создает 2 строки

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

Я надеюсь, что его довольно простая работа, чтобы настроить мой код, чтобы разместить оба диска в одной строке?

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

Это мой запрос

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

Здесь раздел XML как спрошено

<ACME>
  <dbTemplates>
    <ServiceTemplate  >
      <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>

1 ответ

  1. При использовании nodes()на <DriveParameter>вас получить все из них строки мудро. Преимущество было в том, что вы можете справиться с любым номером.

    То, что вы хотите, является результатом side-by-side. В этом случае вы должны знать, какие буквы вы ожидаете и использовать их в качестве XQueryфильтра (и имейте в виду, что XQueryэто чувствительно к регистру. Следующее решение будет работать только с заглавными буквами…

    И еще одна подсказка: проще читать, если вы называете свое CROSS APPLYs likeA(ST)B(RO), C(RT)и так далее. Тогда вы можете позвонить ST.value(...)и избежать повторения .C.valueвашего запроса…

    DECLARE @xml XML=
    '<ACME>
      <dbTemplates>
        <ServiceTemplate  >
          <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>';
    

    —Вопрос

    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