Repeat first element XML SQL Server using for path

Question

I'm generating an XML where the first element needs to be repeated. Currently my code gives following result:

<Item>
    <CompanyCode>VC</CompanyCode>
    <ProfitCenterCode>110050</ProfitCenterCode>
    <ProfitCenterName>Waardeverminderingen Omzet</ProfitCenterName>
    <ExternalCode>VC_110050</ExternalCode>
    <Active>1</Active>
    <Dependencies>
        <Dependency>
            <ExternalCode>VC_85</ExternalCode>
            <BusinessUnitCode>85</BusinessUnitCode>
            <Dependencies>
                <Dependency>
                    <ExternalCode>VC_K0570</ExternalCode>
                    <CostCenterCode>K0570</CostCenterCode>
                </Dependency>
            </Dependencies>
        </Dependency>
        <Dependency>
            <ExternalCode>VC_86</ExternalCode>
            <BusinessUnitCode>86</BusinessUnitCode>
            <Dependencies>
                <Dependency>
                    <ExternalCode>VC_K0570</ExternalCode>
                    <CostCenterCode>K0570</CostCenterCode>
                </Dependency>
            </Dependencies>
        </Dependency>
    </Dependencies>
</Item>

What I want is following code:

<Item>
    <CompanyCode>VC</CompanyCode>
    <ProfitCenterCode>110050</ProfitCenterCode>
    <ProfitCenterName>Waardeverminderingen Omzet</ProfitCenterName>
    <ExternalCode>VC_110050</ExternalCode>
    <Active>1</Active>
    <Dependencies>
        <Dependency>
            <ExternalCode>VC_85</ExternalCode>
            <BusinessUnitCode>85</BusinessUnitCode>
            <Dependencies>
                <Dependency>
                    <ExternalCode>VC_K0570</ExternalCode>
                    <CostCenterCode>K0570</CostCenterCode>
                </Dependency>
            </Dependencies>
        </Dependency>
    </Dependencies>
    <CompanyCode>VC</CompanyCode>
    <ProfitCenterCode>110050</ProfitCenterCode>
    <ProfitCenterName>Waardeverminderingen Omzet</ProfitCenterName>
    <ExternalCode>VC_110050</ExternalCode>
    <Active>1</Active>
    <Dependencies>
        <Dependency>
            <ExternalCode>VC_86</ExternalCode>
            <BusinessUnitCode>86</BusinessUnitCode>
            <Dependencies>
                <Dependency>
                    <ExternalCode>VC_K0570</ExternalCode>
                    <CostCenterCode>K0570</CostCenterCode>
                </Dependency>
            </Dependencies>
        </Dependency>
    </Dependencies>
</Item>

This is the query I'm using:

DECLARE @entity AS NVARCHAR(50)
SET @entity = 'VC'

SELECT
    --UPPER(DIM.DATAAREAID) CompanyCode
    DS.Entities_Cd CompanyCode
    ,DS.DRLE_Reporting_Line_Code ProfitCenterCode
    ,DS.DRLE_Reporting_Line_Descr ProfitCenterName
    ,DS.[DRLE_BKEY] ExternalCode
    ,DS.Is_Actif Active --is altijd active door DS where is_actif = 1
    ,DS.DBUE_BKEY ExternalCodeBU
    ,DS.DBUE_Business_Unit_Code BusinessUnitCode
    ,DS.DCCE_BKEY ExternalCodeCC
    ,DS.DCCE_Cost_Center_Code CostCenterCode
INTO #TEMP
FROM (
   SELECT DISTINCT 
      [DRLE_BKEY]
      ,RL.[DRLE_Reporting_Line_Code]
      ,RL.[DRLE_Reporting_Line_Descr]
      ,BU.DBUE_Business_Unit_Code
      ,BU.DBUE_BKEY
      ,CC.DCCE_Cost_Center_Code
      ,CC.DCCE_BKEY
      ,Entities_Cd
      ,DS.Is_Actif
   FROM [mdm].[Dimensionsets] DS
   LEFT JOIN [mdm].[Dim_Entities] DE
      ON DS.Entities_Key = DE.[Entities_Key]
   LEFT JOIN [mdm].[DIM_Reporting_Lines_Entity] RL
      ON DS.[Reporting_Lines_Key] = RL.DRLE_skey
   LEFT JOIN [mdm].[DIM_Business_Units_Entity] BU
      ON BU.[DBUE_SKEY] = DS.Business_Units_Key
   INNER JOIN [mdm].[DIM_Cost_Centers_Entity] CC
    ON DS.Cost_places_Key = CC.DCCE_SKEY
   WHERE DE.Entities_Cd = UPPER(@entity)
      AND DS.is_actif = 1
      --and DRLE_Reporting_Line_Code = '110050'
) DS

SELECT
    A.CompanyCode
    ,A.ProfitCenterCode
    ,A.ProfitCenterName
    ,A.ExternalCode
    ,A.Active --is altijd active door DS where is_actif = 1
    ,(
      SELECT
       T.ExternalCode
       ,T.BusinessUnitCode
       ,(
          SELECT DISTINCT
             TT.ExternalCodeCC ExternalCode
             ,TT.CostCenterCode
          FROM #TEMP TT
          WHERE A.ProfitCenterCode = TT.ProfitCenterCode AND T.BusinessUnitCode = TT.BusinessUnitCode
          FOR XML PATH('Dependency'), ROOT ('Dependencies'), TYPE
       )
      FROM 
      (
       SELECT DISTINCT
           T.ExternalCodeBU ExternalCode
          ,T.BusinessUnitCode
       FROM #TEMP T
       WHERE A.ProfitCenterCode = T.ProfitCenterCode --AND T.Active = 1
      ) T     
      FOR XML PATH('Dependency'), ROOT ('Dependencies'), TYPE
    )
FROM 
(
    SELECT DISTINCT
        CompanyCode
       ,ProfitCenterCode
       ,ProfitCenterName
       ,ExternalCode
       ,Active
    FROM #TEMP
    WHERE ProfitCenterCode = '110050'
) A
FOR XML PATH ('Item')

If anyone has a clue how to achieve this would be greatly appreciated.

Mikhail Lobanov · Accepted Answer · 2016-12-09 18:16:24Z

You need one row per dependancy in main query. I've join #TEMP T to main table instead of subquery. Try something like this:

DECLARE @entity AS NVARCHAR(50)
SET @entity = 'VC'

SELECT
    --UPPER(DIM.DATAAREAID) CompanyCode
    DS.Entities_Cd CompanyCode
    ,DS.DRLE_Reporting_Line_Code ProfitCenterCode
    ,DS.DRLE_Reporting_Line_Descr ProfitCenterName
    ,DS.[DRLE_BKEY] ExternalCode
    ,DS.Is_Actif Active --is altijd active door DS where is_actif = 1
    ,DS.DBUE_BKEY ExternalCodeBU
    ,DS.DBUE_Business_Unit_Code BusinessUnitCode
    ,DS.DCCE_BKEY ExternalCodeCC
    ,DS.DCCE_Cost_Center_Code CostCenterCode
INTO #TEMP
FROM (
   SELECT DISTINCT 
      [DRLE_BKEY]
      ,RL.[DRLE_Reporting_Line_Code]
      ,RL.[DRLE_Reporting_Line_Descr]
      ,BU.DBUE_Business_Unit_Code
      ,BU.DBUE_BKEY
      ,CC.DCCE_Cost_Center_Code
      ,CC.DCCE_BKEY
      ,Entities_Cd
      ,DS.Is_Actif
   FROM [mdm].[Dimensionsets] DS
   LEFT JOIN [mdm].[Dim_Entities] DE
      ON DS.Entities_Key = DE.[Entities_Key]
   LEFT JOIN [mdm].[DIM_Reporting_Lines_Entity] RL
      ON DS.[Reporting_Lines_Key] = RL.DRLE_skey
   LEFT JOIN [mdm].[DIM_Business_Units_Entity] BU
      ON BU.[DBUE_SKEY] = DS.Business_Units_Key
   INNER JOIN [mdm].[DIM_Cost_Centers_Entity] CC
    ON DS.Cost_places_Key = CC.DCCE_SKEY
   WHERE DE.Entities_Cd = UPPER(@entity)
      AND DS.is_actif = 1
      --and DRLE_Reporting_Line_Code = '110050'
) DS

SELECT

    A.CompanyCode
    ,A.ProfitCenterCode
    ,A.ProfitCenterName
    ,A.ExternalCode
    ,A.Active --is altijd active door DS where is_actif = 1
    ,(
      SELECT
       T_New.ExternalCode
       ,T_New.BusinessUnitCode
       ,(
          SELECT DISTINCT
             TT.ExternalCodeCC ExternalCode
             ,TT.CostCenterCode
          FROM #TEMP TT
          WHERE A.ProfitCenterCode = TT.ProfitCenterCode AND T.BusinessUnitCode = TT.BusinessUnitCode
          FOR XML PATH('Dependency'), ROOT ('Dependencies'), TYPE
       )
      FROM 
      (
       SELECT DISTINCT
           T.ExternalCodeBU ExternalCode
          ,T.BusinessUnitCode
      ) T_New     
      FOR XML PATH('Dependency'), ROOT ('Dependencies'), TYPE
    )
FROM 
(
    SELECT DISTINCT
        CompanyCode
       ,ProfitCenterCode
       ,ProfitCenterName
       ,ExternalCode
       ,Active
    FROM #TEMP
    WHERE ProfitCenterCode = '110050'
) A
INNER JOIN #TEMP T ON A.ProfitCenterCode = T.ProfitCenterCode
FOR XML PATH (''), ROOT('Item')

Collectives™ on Stack Overflow

Repeat first element XML SQL Server using for path

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related