Prevent repeating Elements using For XML Path in T-SQL output-CodePudding

I am currently working on creating XML output via SQL Server for a 3rd party application using T-SQL. I have a specification of what the XML format should like. I am just struggling to get it precisely how it needs to be. I have provided some sample data to explain what is happening.

CREATE TABLE #XML
( 
 party_id NVARCHAR(8)
,data_timestamp datetime
,active_customer bit
,code  NVARCHAR(30)
,[description] NVARCHAR(20)
,date_of_birth date
,initials_np NVARCHAR(20)
,first_name NVARCHAR(20)
,last_name NVARCHAR(20)
,address_type NVARCHAR(20)
,address NVARCHAR(40)
,city NVARCHAR(10)
 )
 INSERT INTO #XML
 (
  party_id 
,data_timestamp 
,active_customer
,code  
,[description] 
,date_of_birth 
,initials_np 
,first_name 
,last_name 
,address_type 
,address 
,city 

 )
 Values('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Primary Address','5 Cod street', 'Amsterdam')
 ,('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Secondary Address','4 Pike street', 'Rotterdam')
 ,('CUST2',GETDATE(),0,'Customer not Found',NULL, NULL, NULL,NULL,NULL,NULL,NULL, NULL)

The query I have so far is

SELECT 
data_timestamp as 'Metadata/data_timestamp'
,code as 'notification/code'
,[description] as   'notification/description'
,date_of_birth as 'register/natural_person/date_of_birth'
,initials_np as 'register/natural_person/name/Initials'
,first_name as   'register/natural_person/name/first_name'
,last_name as  'register/natural_person/name/last_name'
 ,(   SELECT 
            B.city , B.address_type ,
            B.address
            FROM  #XML AS B
            WHERE B.party_id = T.party_id
                FOR XML PATH ('address'), TYPE
            ) 
FROM #XML T
FOR XML PATH, ROOT('bdrp_client_response'), TYPE, ELEMENTS XSINIL;

This query gives me the following output:

<bdrp_client_response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <Metadata>
      <data_timestamp>2022-08-04T22:15:33.537</data_timestamp>
    </Metadata>
    <notification>
      <code>Customer Found</code>
      <description>Valid</description>
    </notification>
    <register>
      <natural_person>
        <date_of_birth>1979-11-05</date_of_birth>
        <name>
          <Initials>JBS</Initials>
          <first_name>John</first_name>
          <last_name>Smith</last_name>
        </name>
      </natural_person>
    </register>
    <address>
      <city>Amsterdam</city>
      <address_type>Primary Address</address_type>
      <address>5 Cod street</address>
    </address>
    <address>
      <city>Rotterdam</city>
      <address_type>Secondary Address</address_type>
      <address>4 Pike street</address>
    </address>
  </row>
  <row>
    <Metadata>
      <data_timestamp>2022-08-04T22:15:33.537</data_timestamp>
    </Metadata>
    <notification>
      <code>Customer Found</code>
      <description>Valid</description>
    </notification>
    <register>
      <natural_person>
        <date_of_birth>1979-11-05</date_of_birth>
        <name>
          <Initials>JBS</Initials>
          <first_name>John</first_name>
          <last_name>Smith</last_name>
        </name>
      </natural_person>
    </register>
    <address>
      <city>Amsterdam</city>
      <address_type>Primary Address</address_type>
      <address>5 Cod street</address>
    </address>
    <address>
      <city>Rotterdam</city>
      <address_type>Secondary Address</address_type>
      <address>4 Pike street</address>
    </address>
  </row>
  <row>
    <Metadata>
      <data_timestamp>2022-08-04T22:15:33.537</data_timestamp>
    </Metadata>
    <notification>
      <code>Customer not Found</code>
      <description xsi:nil="true" />
    </notification>
    <register>
      <natural_person>
        <date_of_birth xsi:nil="true" />
        <name>
          <Initials xsi:nil="true" />
          <first_name xsi:nil="true" />
          <last_name xsi:nil="true" />
        </name>
      </natural_person>
    </register>
    <address />
  </row>
</bdrp_client_response>

The actual response I need is:

<bdrp_client_response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Metadata>
      <data_timestamp>2022-08-04T22:15:33.537</data_timestamp>
    </Metadata>
    <notification>
      <code>Customer Found</code>
      <description>Valid</description>
    </notification>
    <register>
      <natural_person>
        <date_of_birth>1979-11-05</date_of_birth>
        <name>
          <Initials>JBS</Initials>
          <first_name>John</first_name>
          <last_name>Smith</last_name>
        </name>
        <address>
            <city>Amsterdam</city>
            <address_type>Primary Address</address_type>
            <address>5 Cod street</address>
        </address>
        <address>
            <city>Rotterdam</city>
            <address_type>Secondary Address</address_type>
            <address>4 Pike street</address>
        </address>
        </natural_person>
    </register>
    <Metadata>
      <data_timestamp>2022-08-04T22:15:33.537</data_timestamp>
    </Metadata>
    <notification>
      <code>Customer not Found</code>
      <description xsi:nil="true" />
    </notification>
    <register>
      <natural_person xsi:nil="true" />
    </register>
 </bdrp_client_response>

For the data, my code appears to be repeating elements based on the 2 rows for the same customer. The only elements that should be repeated are addresses as customers can have multiple addresses. The other elements (notification and metadata) should only appear once per customer. In the case of the second customer in the sample data, as there are no customer details the address and name elements should not be seen. Only <natural_person xsi:nil="true" />.

Additionally, how can I get rid of the <row></row> elements? Any help would be much appreciated

CodePudding user response：

Please try the following.

SQL

DECLARE @tbl TABLE ( 
     party_id NVARCHAR(8)
    ,data_timestamp datetime
    ,active_customer bit
    ,code  NVARCHAR(30)
    ,[description] NVARCHAR(20)
    ,date_of_birth date
    ,initials_np NVARCHAR(20)
    ,first_name NVARCHAR(20)
    ,last_name NVARCHAR(20)
    ,address_type NVARCHAR(20)
    ,address NVARCHAR(40)
    ,city NVARCHAR(10)
 );
 INSERT INTO @tbl
 (
      party_id 
    ,data_timestamp 
    ,active_customer
    ,code  
    ,[description] 
    ,date_of_birth 
    ,initials_np 
    ,first_name 
    ,last_name 
    ,address_type 
    ,address 
    ,city 
 )
VALUES
('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Primary Address','5 Cod street', 'Amsterdam'),
('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Secondary Address','4 Pike street', 'Rotterdam'),
('CUST2',GETDATE(),0,'Customer not Found',NULL, NULL, NULL,NULL,NULL,NULL,NULL, NULL);

;WITH rs AS
(
    SELECT party_id, data_timestamp 
        ,active_customer
        ,code  
        ,[description] 
        ,date_of_birth 
        ,initials_np 
        ,first_name 
        ,last_name 
    FROM @tbl
    GROUP BY party_id, data_timestamp 
        ,active_customer
        ,code  
        ,[description] 
        ,date_of_birth 
        ,initials_np 
        ,first_name 
        ,last_name 
)
SELECT data_timestamp as 'Metadata/data_timestamp'
    ,code as 'notification/code'
    ,[description] as   'notification/description'
    ,date_of_birth as 'register/natural_person/date_of_birth'
    ,initials_np as 'register/natural_person/name/Initials'
    ,first_name as   'register/natural_person/name/first_name'
    ,last_name as  'register/natural_person/name/last_name'
     ,(SELECT B.city, B.address_type ,
            B.address
            FROM  @tbl AS B
            WHERE B.party_id = T.party_id
            FOR XML PATH ('address'), TYPE
    ) 
FROM rs AS t
FOR XML PATH(''), ROOT('bdrp_client_response'), TYPE, ELEMENTS XSINIL;