Форматирование XML SQL Server

Я застрял на этой проблеме почти 2 дня.. в самом низу находится то, что я придумал, но я не могу правильно форматировать. Любая помощь была бы очень признательна.

Вот в чем проблема:

--Axis data
create TABLE tmpPrimaryAxisLeaves 
(
     NodeID int, 
     NodeLabel nvarchar(max)
)

create TABLE tmpSecondaryAxisLeaves  
(
     NodeID int, 
     NodeLabel nvarchar(max)
)

create TABLE tmpTertiaryAxisLeaves 
(
     NodeID int, 
     NodeLabel nvarchar(max)
)

INSERT INTO tmpPrimaryAxisLeaves (NodeID, NodeLabel) 
    SELECT 1, 'North America' UNION
    SELECT 2, 'South America' UNION
    SELECT 3, 'EU' UNION
    SELECT 4, 'Africa' UNION
    SELECT 5, 'Russia'

INSERT INTO tmpSecondaryAxisLeaves (NodeID, NodeLabel) 
    SELECT 10, 'Peter Pan' UNION
    SELECT 20, 'Groot' UNION
    SELECT 30, 'Batman' 

INSERT INTO tmpTertiaryAxisLeaves (NodeID, NodeLabel) 
    SELECT 2, 'Shirts' UNION
    SELECT 4, 'Pants' UNION
    SELECT 6, 'Hats' 

--Contract data
create table tmpContracts 
(
     StartDate datetime, 
     EndDate datetime, 
     PrimaryAxisID int, 
     SecondaryAxisID int, 
     TertiaryAxisID int, 
     LanguageID int
)

INSERT INTO tmpContracts (StartDate, EndDate, PrimaryAxisID, SecondaryAxisID, TertiaryAxisID, LanguageID)
  SELECT '1/1/2010', '1/1/2018', 1, 20, 2, 1 UNION
  SELECT '1/1/2010', '1/1/2018', 1, 20, 2, 2 UNION
  SELECT '1/1/2010', '1/1/2018', 1, 20, 2, 5 UNION
  SELECT '1/1/2010', '1/1/2018', 1, 20, 6, 1 UNION
  SELECT '1/1/2010', '1/1/2018', 1, 20, 6, 2 UNION
  SELECT '1/1/2010', '1/1/2018', 1, 20, 6, 5 UNION
  SELECT '1/1/2011', '1/1/2020', 2, 20, 2, 1 UNION
  SELECT '1/1/2011', '1/1/2020', 2, 20, 2, 3 UNION
  SELECT '1/1/2011', '1/1/2020', 2, 20, 4, 1 UNION
  SELECT '1/1/2011', '1/1/2020', 2, 20, 4, 3 UNION
  SELECT '1/1/2011', '1/1/2020', 2, 20, 4, 5 UNION
  SELECT '1/1/2011', '1/1/2020', 3, 30, 2, 1 UNION
  SELECT '1/1/2011', '1/1/2020', 4, 30, 6, 1

--Langauges
create table tmpLanguages 
(
     LanguageID int, 
     Name nvarchar(100)
)

INSERT INTO tmpLanguages
   SELECT 1, 'English' UNION
   SELECT 2, 'Spanish' UNION
   SELECT 3, 'French' UNION
   SELECT 4, 'Russia' UNION
   SELECT 5, 'Dutch'

Напишите запрос, который примет вышеуказанные данные и вернет XML в следующем формате с правильными значениями в каждом указанном месте («[XXXXX]») результаты должны понравиться это:

<AvailabiltyList>
  <PrimaryNode>
    <NodeID>[PrimaryAxis.NodeID]</NodeID>
    <NodeLabel>[PrimaryAxis.NodeLabel]</NodeLabel>
    <SecondaryAxis>
      <SecondaryNode>
        <NodeID>[SecondaryAxis.NodeID]</NodeID>
        <NodeLabel>[SecondaryAxis.NodeLabel]</NodeLabel>
        <TertiaryAxis>
          <TertiaryNode>
            <NodeID>[TertiaryAxis.NodeID]</NodeID>
            <NodeLabel>[TertiaryAxis.NodeLabel]</NodeLabel>
            <ContractData>
              <ContractDataPoint>
                <ContractStart>[Contracts.StartDate]</ContractStart>
                <ContractEnd>[Contracts.EndDate]</ContractEnd>
              </ContractDataPoint>
            </ContractData>
            <Languages>[Comma delimited list of languages for the matching contracts]</Languages>
          </TertiaryNode>
          <TertiaryNode>....</TertiaryNode>
          <TertiaryNode>....</TertiaryNode>
          <TertiaryNode>....</TertiaryNode>
        </TertiaryAxis>
      </SecondaryNode>
      <SecondaryNode>...</SecondaryNode>
      <SecondaryNode>...</SecondaryNode>
      <SecondaryNode>...</SecondaryNode>
      <SecondaryNode>...</SecondaryNode>
  </PrimaryNode>
  <PrimaryNode>...</PrimaryNode>
  <PrimaryNode>...</PrimaryNode>
  <PrimaryNode>...</PrimaryNode>
</AvailabilityList>

Вот что я придумал..но я не могу правильно форматировать.

select 
    PrimaryNode.NodeID as pnode,
    PrimaryNode.NodeLabel as plabel,
    SecondaryNode.NodeID as snode,
    SecondaryNode.NodeLabel as slabel,
    TertiaryNode.NodeID as tnode,
    TertiaryNode.NodeLabel as tlable, 
    ContractDataPoint.StartDate, 
    ContractDataPoint.EndDate
from 
    tmpContracts ContractDataPoint 
left join 
    tmpPrimaryAxisLeaves PrimaryNode on ContractDataPoint.PrimaryAxisID = PrimaryNode.NodeID
inner join 
    tmpSecondaryAxisLeaves SecondaryNode on     ContractDataPoint.SecondaryAxisID = SecondaryNode.NodeID
inner join 
    tmpTertiaryAxisLeaves TertiaryNode on        ContractDataPoint.TertiaryAxisID = TertiaryNode.NodeID
group by 
    PrimaryNode.NodeID, PrimaryNode.NodeLabel,
    SecondaryNode.NodeID, SecondaryNode.NodeLabel,
    TertiaryNode.NodeID, TertiaryNode.NodeLabel, 
    ContractDataPoint.StartDate, ContractDataPoint.EndDate
for xml auto, Root('AvailabiltyList'), Elements

Мои результаты:

<AvailabiltyList>
    <PrimaryNode>
        <pnode>1</pnode>
        <plabel>North America</plabel>
        <SecondaryNode>
          <snode>20</snode>
          <slabel>Groot</slabel>
      <TertiaryNode>
        <tnode>2</tnode>
        <tlable>Shirts</tlable>
        <ContractDataPoint>
          <StartDate>2010-01-01T00:00:00</StartDate>
          <EndDate>2018-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
      <TertiaryNode>
        <tnode>6</tnode>
        <tlable>Hats</tlable>
        <ContractDataPoint>
          <StartDate>2010-01-01T00:00:00</StartDate>
          <EndDate>2018-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
    </SecondaryNode>
  </PrimaryNode>
  <PrimaryNode>
    <pnode>2</pnode>
    <plabel>South America</plabel>
    <SecondaryNode>
      <snode>20</snode>
      <slabel>Groot</slabel>
      <TertiaryNode>
        <tnode>2</tnode>
        <tlable>Shirts</tlable>
        <ContractDataPoint>
          <StartDate>2011-01-01T00:00:00</StartDate>
          <EndDate>2020-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
      <TertiaryNode>
        <tnode>4</tnode>
        <tlable>Pants</tlable>
        <ContractDataPoint>
          <StartDate>2011-01-01T00:00:00</StartDate>
          <EndDate>2020-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
    </SecondaryNode>
  </PrimaryNode>
  <PrimaryNode>
    <pnode>3</pnode>
    <plabel>EU</plabel>
    <SecondaryNode>
      <snode>30</snode>
      <slabel>Batman</slabel>
      <TertiaryNode>
        <tnode>2</tnode>
        <tlable>Shirts</tlable>
        <ContractDataPoint>
          <StartDate>2011-01-01T00:00:00</StartDate>
          <EndDate>2020-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
    </SecondaryNode>
  </PrimaryNode>
  <PrimaryNode>
    <pnode>4</pnode>
    <plabel>Africa</plabel>
    <SecondaryNode>
      <snode>30</snode>
      <slabel>Batman</slabel>
      <TertiaryNode>
        <tnode>6</tnode>
        <tlable>Hats</tlable>
        <ContractDataPoint>
          <StartDate>2011-01-01T00:00:00</StartDate>
          <EndDate>2020-01-01T00:00:00</EndDate>
        </ContractDataPoint>
      </TertiaryNode>
    </SecondaryNode>
  </PrimaryNode>
</AvailabiltyList>

1 ответ

  1. Вы можете попробовать что-то вроде этого

    Создание udf для получения языков

    CREATE FUNCATION udfAxisLanguage
    (@pNodeId  INT
    ,@sNodeId  INT
    ,@tNodeId  INT
    )
    RETURNS VARCHAR(100)
    AS
    BEGIN
    DECLARE @retLang VARCHAR(100)   
    select  
        @retLang = COALESCE(@retLang + ',','') + lang.name
    from 
        tmpContracts ContractDataPoint 
    left join 
        tmpPrimaryAxisLeaves PrimaryNode on ContractDataPoint.PrimaryAxisID = PrimaryNode.NodeID
    inner join 
        tmpSecondaryAxisLeaves SecondaryNode on     ContractDataPoint.SecondaryAxisID = SecondaryNode.NodeID
    inner join 
        tmpTertiaryAxisLeaves TertiaryNode on        ContractDataPoint.TertiaryAxisID = TertiaryNode.NodeID
    inner join tmpLanguages lang on ContractDataPoint.languageid = lang.languageId
    WHERE PrimaryNode.NodeID = @pNodeId
    AND   SecondaryNode.NodeID = @sNodeId
    AND   TertiaryNode.NodeID = @tNodeId
    ;
    
    RETURN @retLang ;
    END ;
    

    Теперь используйте вышеупомянутый udf в своем запросе, как это

    select 
        PrimaryNode.NodeID as pnode,
        PrimaryNode.NodeLabel as plabel,
        SecondaryNode.NodeID as snode,
        SecondaryNode.NodeLabel as slabel,
        TertiaryNode.NodeID as tnode,
        TertiaryNode.NodeLabel as tlable, 
        ContractDataPoint.StartDate, 
        ContractDataPoint.EndDate,
        dbo.udfAxisLanguage(PrimaryNode.NodeID,SecondaryNode.NodeID,TertiaryNode.NodeID) Name
    from 
        tmpContracts ContractDataPoint 
    left join 
        tmpPrimaryAxisLeaves PrimaryNode on ContractDataPoint.PrimaryAxisID = PrimaryNode.NodeID
    inner join 
        tmpSecondaryAxisLeaves SecondaryNode on     ContractDataPoint.SecondaryAxisID = SecondaryNode.NodeID
    inner join 
        tmpTertiaryAxisLeaves TertiaryNode on        ContractDataPoint.TertiaryAxisID = TertiaryNode.NodeID
    group by 
        PrimaryNode.NodeID, PrimaryNode.NodeLabel,
        SecondaryNode.NodeID, SecondaryNode.NodeLabel,
        TertiaryNode.NodeID, TertiaryNode.NodeLabel, 
        ContractDataPoint.StartDate, ContractDataPoint.EndDate
        ,dbo.udfAxisLanguage(PrimaryNode.NodeID,SecondaryNode.NodeID,TertiaryNode.NodeID)
    for xml auto, Root('AvailabiltyList'), Elements