locked
complex node xml output file using sql data table. RRS feed

  • Question

  • Hi All,

    Now I am correct forum.

    i am trying to build xml file with following tables and data.. I have already got middle part (address and phone) but not sure how to get final xml as below..

    Need out put as

    1. if for same identifier having one address and two phone numbers then it should appear in 

            <addressPhoneList>
              <telephone>
                <phoneCountryCode>1</phoneCountryCode>
                <phoneAreaCode>333</phoneAreaCode>
                <phoneNumber>87888</phoneNumber>
                <phoneType>
                  <id>Work Phone Number</id>
                  <type>Cell</type>
                </phoneType>
              </telephone>
              <telephone>
                <phoneCountryCode>1</phoneCountryCode>
                <phoneAreaCode>555</phoneAreaCode>
                <phoneNumber>87999</phoneNumber>
                <phoneType>
                  <id>Work Phone Number</id>
                  <type>Fax</type>
                </phoneType>
              </telephone>
            </addressPhoneList>

    2. Header details should be shown only once at top. It has details for count of all the vendors.

    please help. T.I.A

    -- Code start

    -- header information table  Need this info at start of file output only once
    create table #headerdetails 
    (totalRecordCount int
    , correlationId varchar(255)
    , senderid varchar(255)
    , receiverId int)

    insert into #headerdetails
    select 1, 'CARIL0200115-1','144R',437

    -- vendor table unique Identifier records need to store 
    create table #v (Identifier varchar(100))

    insert into #v
    select '9876263'

    insert into #v
    select '9876264'

    -- vendor details table
    create table #vd (Identifier varchar(100),add_id int, other_Address varchar(50)
    , phoneNumber varchar(100), PhoneTypeid varchar(100)
    ,identificationType varchar(255), orderStartDate varchar(20),NId varchar(15))

    INSERT INTO @vd VALUES 
    ('9876263', 101, '56 xyz Rd', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234');

    INSERT INTO @vd VALUES 
    ('9876263', 101, '56 xyz Rd', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234');

    INSERT INTO @vd VALUES 
    ('9876264', 103, '45 xyz Rd', '31xx222', 'office', 'Highway part Company_N', '2016-01-01', 'N1235');

    -- code end

    xml output file as

    <?xml version="1.0" encoding="UTF-8"?>
    <carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header xmlns="http://www.global.com/schema/common">
    <totalRecordCount xmlns="">2</totalRecordCount>
    <fileCreationDate xmlns="">2018-01-15</fileCreationDate>
    <correlationId xmlns="">CARIL0200115-1</correlationId>
    <senderId xmlns="">144R</senderId>
    <receiverId xmlns="">437</receiverId>
    </header>
    <carMaintenanceServiceStronglyTypedIBRequest>
    <createVendorRecordList>
      <recordCount>2</recordCount>
      <createVendorRecord>
        <recordId>1</recordId>
        <createVendor xmlns="http://mnc.com">
          <Vendor xmlns="">
            <orderStartDate>2016-01-01</orderStartDate>
            <Identifier>9876263</Identifier>
            <NId>N1234</NId>
            <primaryId>
              <identificationNumber>9876263</identificationNumber>
              <identificationType>
                <id>Highway part Company</id>
              </identificationType>
            </primaryId>   
            <otherCorrespondenceAddressList operation="merge">
              <correspondenceInformation>
                <addressInformation>
                  <postalAddress>
                    <address>56 xyz Rd</address>
                  </postalAddress>
      <addressPhoneList>
                   <telephone>
                     <phoneNumber>31xx646</phoneNumber>
                     <phoneType>
                       <id>Mobile</id>
                     </phoneType>
                    </telephone>
                   <telephone>
                     <phoneNumber>31xx123</phoneNumber>
                     <phoneType>
                       <id>Office</id>
                     </phoneType>
                    </telephone>
                   </addressPhoneList>   
                </addressInformation>
              </correspondenceInformation>
            </otherCorrespondenceAddressList>
          </Vendor>
          <asOfDate xmlns="">1900-01-01</asOfDate>
        </createVendor>
      </createVendorRecord>
      <createVendorRecord>
        <recordId>2</recordId>
        <createVendor xmlns="http://mnc.com">
          <Vendor xmlns="">
            <orderStartDate>2016-01-01</orderStartDate>
            <Identifier>9876264</Identifier>
            <NId>N1235</NId>
            <primaryId>
              <identificationNumber>9876264</identificationNumber>
              <identificationType>
                <id>Highway part Company_N</id>
              </identificationType>
            </primaryId>   
            <otherCorrespondenceAddressList operation="merge">
              <correspondenceInformation>
                <addressInformation>
                  <postalAddress>
                    <address>45 xyz Rd</address>
                  </postalAddress>
      <addressPhoneList>
                   <telephone>
                     <phoneNumber>31xx222</phoneNumber>
                     <phoneType>
                       <id>Office</id>
                     </phoneType>
                    </telephone>
                   </addressPhoneList>   
                </addressInformation>
              </correspondenceInformation>
            </otherCorrespondenceAddressList>
          </Vendor>
          <asOfDate xmlns="">1900-01-01</asOfDate>
        </createVendor>
      </createVendorRecord>  
    </createVendorRecordList>
    </carMaintenanceServiceStronglyTypedIBRequest>
    </carMaintenanceServiceRequestRoot>


    T.I.A

    Thursday, July 30, 2020 2:34 PM

Answers

  • Hi papillon28,

    Please try the following solution.

    1. The raw XML is matching the requirements. Check it out
    2. NULL values for the ZIP and entire phone fragment are taken care.
    3. The final XML is matching  the desired output.


    SQL:

    -- DDL and sample data population, start
    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (3, 'CARIL0200115-1', '144R', 437);
    
    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264');
    
    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),
    	Zip varchar(10),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd','56666', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd','56666', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 102, '50 xyz Rd','54444', '31xx555', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876264', 103, '45 xyz Rd', NULL,   '31xx222', 'office', 'Highway part Company_N', '2017-01-01', 'N1235'),
    ('9876265', 104, '10 xyz Rd', NULL, NULL,  NULL, 'ABC Corp', '2018-01-01', 'N1236');
    -- DDL and sample data population, end
    
    -- just to see it
    SELECT * FROM @headerdetails;
    SELECT * FROM @v; 
    SELECT * FROM @vd;
    
    DECLARE @fileCreationDate DATE = GETDATE();
    
    -- real deal
    SELECT (
    SELECT -- create root tag on the fly
    (
    	SELECT *
    	FROM @headerdetails
    	FOR XML PATH(''), TYPE, ROOT('header')
    )
    , (
    	SELECT Identifier, identificationType, orderStartDate, NId
    		, ROW_NUMBER() OVER(ORDER BY Identifier) AS seq
    		, (
    			SELECT Identifier, add_id, other_Address, Zip
    				, (
    				SELECT add_id, phoneNumber, PhoneTypeid
    				FROM @vd AS ph
    				WHERE ad.add_id = ph.add_id
    				FOR XML PATH('phone'), TYPE, ROOT('phones')
    			)			
    			FROM (SELECT DISTINCT Identifier, add_id, other_Address, Zip FROM @vd) AS ad
    			WHERE ad.Identifier = vd.Identifier
    			FOR XML PATH('address'), TYPE, ROOT('addresses')
    		)
    	FROM (SELECT DISTINCT Identifier, identificationType, orderStartDate, NId
    	FROM @vd) AS vd
    	FOR XML PATH('r'), TYPE, ROOT('vendors')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance">
    {
    	for $h in /*:root/*:header
    	return (<header xmlns="http://www.global.com/schema/common">
    				<totalRecordCount>{data($h/*:totalRecordCount)}</totalRecordCount>
    				<fileCreationDate>{sql:variable("@fileCreationDate")}</fileCreationDate>
    				<correlationId>{data($h/*:correlationId)}</correlationId>
    				<senderId>{data($h/*:senderid)}</senderId>
    				<receiverId>{data($h/*:receiverId)}</receiverId>
    			</header>,
    			<carMaintenanceServiceStronglyTypedIBRequest>
    					<createVendorRecordList>
    						<recordCount>{count(/*:root/*:vendors/*:r)}</recordCount>
    						{
    							for $x in /*:root/*:vendors/*:r
    							return <createVendorRecord>
    										<recordId>{data($x/*:seq)}</recordId>
    										<createVendor xmlns="http://mnc.com">
    											<Vendor>
    												<orderStartDate>{data($x/*:orderStartDate)}</orderStartDate>
    												<Identifier>{data($x/*:Identifier)}</Identifier>
    												<NId>{data($x/*:NId)}</NId>
    												<primaryId>
    													<identificationNumber>{data($x/*:Identifier)}</identificationNumber>
    													<identificationType>
    														<id>Highway part Company</id>
    													</identificationType>
    												</primaryId>
    												<otherCorrespondenceAddressList operation="merge">
    													<correspondenceInformation>
    													{
    													for $ad in $x/*:addresses/*:address
    													return <addressInformation>
    															<postalAddress>
    																<address>{data($ad/*:other_Address)}</address>
    																{
    																if ($ad/*:Zip[string-length(text()[1]) gt 0]) then
    																	<zip>{data($ad/*:Zip)}</zip>
    																else ()
    																}
    															</postalAddress>
    															{
    															if ($ad/*:phones/*:phone/*:phoneNumber[string-length(text()[1]) gt 0]) then
    																<addressPhoneList>
    																{
    																for $ph in $ad/*:phones/*:phone
    																return
    																	<telephone>
    																		<phoneNumber>{data($ph/*:phoneNumber)}</phoneNumber>
    																		<phoneType>
    																			<id>{data($ph/*:PhoneTypeid)}</id>
    																		</phoneType>
    																	</telephone>
    																	}
    																</addressPhoneList>
    															else ()
    															}
    														</addressInformation>
    													}
    													</correspondenceInformation>
    												</otherCorrespondenceAddressList>
    											</Vendor>
    											<asOfDate>1900-01-01</asOfDate>
    										</createVendor>
    								</createVendorRecord>
    						}
    					</createVendorRecordList>
    				</carMaintenanceServiceStronglyTypedIBRequest>
    			)
    }
    </carMaintenanceServiceRequestRoot>');

    • Edited by Yitzhak Khabinsky Friday, July 31, 2020 2:18 PM
    • Marked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    • Unmarked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    • Marked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    Friday, July 31, 2020 2:15 PM

All replies

  • I don't see how your T-SQL sample is correlated to your data. What you can do with it:

    DECLARE @vd TABLE (
        Identifier VARCHAR(100) ,
        add_id INT ,
        other_Address VARCHAR(50) ,
        phoneNumber VARCHAR(100) ,
        PhoneTypeid VARCHAR(100) ,
        identificationType VARCHAR(255) ,
        orderStartDate VARCHAR(20) ,
        NId VARCHAR(15)
    );
    
    INSERT INTO @vd
    VALUES ( '9876263', 101, '56 xyz Rd', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234' ) ,
           ( '9876263', 101, '56 xyz Rd', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234' ) ,
           ( '9876264', 103, '45 xyz Rd', '31xx222', 'office', 'Highway part Company_N', '2016-01-01', 'N1235' );
    
    SELECT   V.Identifier ,
             (   SELECT *
                 FROM   @vd V2
                 WHERE  V2.Identifier = V.Identifier
                 FOR XML PATH('Phone'), TYPE ) AS [Phones]
    FROM     @vd V
    GROUP BY V.Identifier
    FOR XML PATH('PhoneList');
    Result:
    <PhoneList>
      <Identifier>9876263</Identifier>
      <Phone>
        <Phones>
          <Identifier>9876263</Identifier>
          <add_id>101</add_id>
          <other_Address>56 xyz Rd</other_Address>
          <phoneNumber>31xx646</phoneNumber>
          <PhoneTypeid>Mobile</PhoneTypeid>
          <identificationType>Highway part Company</identificationType>
          <orderStartDate>2016-01-01</orderStartDate>
          <NId>N1234</NId>
        </Phones>
        <Phones>
          <Identifier>9876263</Identifier>
          <add_id>101</add_id>
          <other_Address>56 xyz Rd</other_Address>
          <phoneNumber>31xx123</phoneNumber>
          <PhoneTypeid>office</PhoneTypeid>
          <identificationType>Highway part Company</identificationType>
          <orderStartDate>2016-01-01</orderStartDate>
          <NId>N1234</NId>
        </Phones>
      </Phone>
    </PhoneList>
    <PhoneList>
      <Identifier>9876264</Identifier>
      <Phone>
        <Phones>
          <Identifier>9876264</Identifier>
          <add_id>103</add_id>
          <other_Address>45 xyz Rd</other_Address>
          <phoneNumber>31xx222</phoneNumber>
          <PhoneTypeid>office</PhoneTypeid>
          <identificationType>Highway part Company_N</identificationType>
          <orderStartDate>2016-01-01</orderStartDate>
          <NId>N1235</NId>
        </Phones>
      </Phone>
    </PhoneList>

    Thursday, July 30, 2020 3:04 PM
  • Thank you for reply. But need like below header and namespace part show only once at start. <?xml version="1.0" encoding="UTF-8"?> <carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <header xmlns="http://www.global.com/schema/common"> <totalRecordCount xmlns="">2</totalRecordCount> <fileCreationDate xmlns="">2018-01-15</fileCreationDate> <correlationId xmlns="">CARIL0200115-1</correlationId> <senderId xmlns="">144R</senderId> <receiverId xmlns="">43 And totalRecordCount is total number of identifiers in table. Below header part Rest all tags are as is as shown in my original post.. this one file output is most complex one compared to my other filed.. T.I.A
    Thursday, July 30, 2020 3:29 PM
  • Well, just do it. But probably you should do a "simple" XLM export by T-SQL and do the rest as XSLT transform.
    Thursday, July 30, 2020 3:32 PM
  • Hi papillon28,

    Please try the following T-SQL with XQuery and its powerful FLWOR expression.

    It is using a 2-step approach:

    1. SELECT ... FOR XML PATH() ... creates a preliminary raw XML.
    2. XQuery and FLWOR expression produce a refined XML based on the desired output.

    SQL:

    -- DDL and sample data population, start
    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (2, 'CARIL0200115-1', '144R', 437);
    
    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264');
    
    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876264', 103, '45 xyz Rd', '31xx222', 'office', 'Highway part Company_N', '2016-01-01', 'N1235');
    -- DDL and sample data population, end
    
    -- just to see it
    SELECT * FROM @headerdetails;
    SELECT * FROM @v;
    SELECT * FROM @vd;
    
    DECLARE @fileCreationDate DATE = GETDATE();
    
    -- real deal
    SELECT (
    SELECT NULL,
    (
    	SELECT *
    	FROM @headerdetails
    	FOR XML PATH(''), TYPE, ROOT('header')
    )
    , (
    	SELECT Identifier, add_id, other_Address, identificationType, orderStartDate, NId
    		, ROW_NUMBER() OVER(ORDER BY Identifier) AS seq
    		, (
    		SELECT Identifier, phoneNumber, PhoneTypeid
    		FROM @vd AS ph
    		WHERE vd.Identifier = ph.Identifier
    		FOR XML PATH('phone'), TYPE, ROOT('phones')
    	)
    	FROM (SELECT DISTINCT Identifier, add_id, other_Address, identificationType, orderStartDate, NId
    	FROM @vd) AS vd
    	FOR XML PATH('r'), TYPE, ROOT('vendors')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance">
    {
    	for $h in /*:root/*:header
    	return (<header xmlns="http://www.global.com/schema/common">
    				<totalRecordCount>{data($h/*:totalRecordCount)}</totalRecordCount>
    				<fileCreationDate>{sql:variable("@fileCreationDate")}</fileCreationDate>
    				<correlationId>{data($h/*:correlationId)}</correlationId>
    				<senderId>{data($h/*:senderid)}</senderId>
    				<receiverId>{data($h/*:receiverId)}</receiverId>
    			</header>,
    			<carMaintenanceServiceStronglyTypedIBRequest>
    					<createVendorRecordList>
    						<recordCount>{count(/*:root/*:vendors/*:r)}</recordCount>
    						{
    							for $x in /*:root/*:vendors/*:r
    							return <createVendorRecord>
    										<recordId>{data($x/*:seq)}</recordId>
    										<createVendor xmlns="http://mnc.com">
    											<Vendor>
    												<orderStartDate>{data($x/*:orderStartDate)}</orderStartDate>
    												<Identifier>{data($x/*:Identifier)}</Identifier>
    												<NId>{data($x/*:NId)}</NId>
    												<primaryId>
    													<identificationNumber>{data($x/*:Identifier)}</identificationNumber>
    													<identificationType>
    														<id>Highway part Company</id>
    													</identificationType>
    												</primaryId>
    												<otherCorrespondenceAddressList operation="merge">
    													<correspondenceInformation>
    														<addressInformation>
    															<postalAddress>
    																<address>{data($x/*:other_Address)}</address>
    															</postalAddress>
    															<addressPhoneList>
    															{
    															for $ph in $x/*:phones/*:phone
    															return
    																<telephone>
    																	<phoneNumber>{data($ph/*:phoneNumber)}</phoneNumber>
    																	<phoneType>
    																		<id>{data($ph/*:PhoneTypeid)}</id>
    																	</phoneType>
    																</telephone>
    															}
    															</addressPhoneList>
    														</addressInformation>
    													</correspondenceInformation>
    												</otherCorrespondenceAddressList>
    											</Vendor>
    											<asOfDate>1900-01-01</asOfDate>
    										</createVendor>
    								</createVendorRecord>
    						}
    					</createVendorRecordList>
    				</carMaintenanceServiceStronglyTypedIBRequest>
    			)
    }
    </carMaintenanceServiceRequestRoot>');

    Thursday, July 30, 2020 3:58 PM
  • Hi Stefan,

    Please connect with me on LinkedIn.

    Thursday, July 30, 2020 4:07 PM
  • Hi Yitzhak,

    Thank you it worked for data but when I have added one more address for same identifier then Phone numbers are appearing under same address.. So I have modified the preliminary raw query and it is showing perfect but when it added with XQuery and FLWOR expression then again not getting desired output..

    Here what I did

    -- DDL and sample data population, start
    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (2, 'CARIL0200115-1', '144R', 437);

    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264');

    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 102, '50 xyz Rd', '31xx124', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876264', 103, '45 xyz Rd', '31xx222', 'office', 'Highway part Company_N', '2016-01-01', 'N1235');
    -- DDL and sample data population, end

    -- just to see it
    --SELECT * FROM @headerdetails;
    --SELECT * FROM @v;
    --SELECT * FROM @vd;

    DECLARE @fileCreationDate DATE = GETDATE();

    -- real deal
    SELECT (
    SELECT NULL,
    (
    SELECT *
    FROM @headerdetails
    FOR XML PATH(''), TYPE, ROOT('header')
    )
    , (
    SELECT Identifier,
    ( select add_id, other_Address, identificationType, orderStartDate, NId
    , ROW_NUMBER() OVER(ORDER BY Identifier) AS seq
    , (
    SELECT Identifier, phoneNumber, PhoneTypeid
    FROM @vd AS ph
    WHERE vd.Identifier = ph.Identifier and vd.add_id = ph.add_id
    FOR XML PATH('phone'), TYPE, ROOT('phones')
    )
    FROM (SELECT DISTINCT Identifier, add_id, other_Address, identificationType, orderStartDate, NId
    FROM @vd) AS vd
    where vd.Identifier = v.Identifier
    FOR XML PATH('address'), TYPE, ROOT('addresses')

    from @v as v
    where final.Identifier = v.Identifier
    FOR XML PATH('r'), TYPE, ROOT('vendors')
    )
    from @v final
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance">
    {
    for $h in /*:root/*:header
    return (<header xmlns="http://www.global.com/schema/common">
    <totalRecordCount>{data($h/*:totalRecordCount)}</totalRecordCount>
    <fileCreationDate>{sql:variable("@fileCreationDate")}</fileCreationDate>
    <correlationId>{data($h/*:correlationId)}</correlationId>
    <senderId>{data($h/*:senderid)}</senderId>
    <receiverId>{data($h/*:receiverId)}</receiverId>
    </header>,
    <carMaintenanceServiceStronglyTypedIBRequest>
    <createVendorRecordList>
    <recordCount>{count(/*:root/*:vendors/*:r)}</recordCount>
    {
    for $x in /*:root/*:vendors/*:r
    return <createVendorRecord>
    <recordId>{data($x/*:seq)}</recordId>
    <createVendor xmlns="http://mnc.com">
    <Vendor>
    <orderStartDate>{data($x/*:orderStartDate)}</orderStartDate>
    <Identifier>{data($x/*:Identifier)}</Identifier>
    <NId>{data($x/*:NId)}</NId>
    <primaryId>
    <identificationNumber>{data($x/*:Identifier)}</identificationNumber>
    <identificationType>
    <id>Highway part Company</id>
    </identificationType>
    </primaryId>
    <otherCorrespondenceAddressList operation="merge">
    <correspondenceInformation>
    <addressInformation>
    <postalAddress>
    <address>{data($x/*:other_Address)}</address>
    </postalAddress>
    <addressPhoneList>
    {
    for $ph in $x/*:phones/*:phone
    return
    <telephone>
    <phoneNumber>{data($ph/*:phoneNumber)}</phoneNumber>
    <phoneType>
    <id>{data($ph/*:PhoneTypeid)}</id>
    </phoneType>
    </telephone>
    }
    </addressPhoneList>
    </addressInformation>
    </correspondenceInformation>
    </otherCorrespondenceAddressList>
    </Vendor>
    <asOfDate>1900-01-01</asOfDate>
    </createVendor>
    </createVendorRecord>
    }
    </createVendorRecordList>
    </carMaintenanceServiceStronglyTypedIBRequest>
    )
    }
    </carMaintenanceServiceRequestRoot>');


    T.I.A

    Thursday, July 30, 2020 6:40 PM
  • Hi papillon28,

    While asking a question you should provide a minimal reproducible example:

    1. DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
    2. What you need to do, i.e. logic, and your attempt of its implementation in T-SQL.
    3. Desired output based on the sample data in #1 above.
    4. Your SQL Server version (SELECT @@version;)


    I answered your original question in this post.

    All of a sudden, a vendor might have multiple addresses. It is a completely new unexpected scenario. The problem is in the @vd table design. It seems that it lumps together 3 entities: vendor, address, and phones.

    So you changed #1, and a desired output (#3) became unknown for that new scenario.

    You need to provide ##1-4 again as a set, and I will gladly help you.

    Thursday, July 30, 2020 8:59 PM
  • Ok here are details.

    -- DDL and sample data population, start

    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (2, 'CARIL0200115-1', '144R', 437);

    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264');

    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),

        Zip varchar(10),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd','56666', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd','56666', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 102, '50 xyz Rd','54444', '31xx555', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),

    ('9876264', 103, '45 xyz Rd', NULL,'31xx222', 'office', 'Highway part Company_N', '2017-01-01', 'N1235');

    ('9876265', 104, '10 xyz Rd', NULL, NULL, 'ABC Corp', '2018-01-01', 'N1236');
    -- DDL and sample data population, end

    Here want I needed

    • below output where 
    1. one vendor has one or multiple addresses.  -- Identifier = 9876263
    2. one address has one or multiple phones     -- Identifier = 9876263
    3. If address exists but phone data is null then only address element need to show -- Identifier  = 9876265
    4. If any of the attribute data is NULL then don't show that attribute -- Identifier = 9876264 (zip = null)
    5. If I need to add one more list as below then how to merge two files or in single query will work with union? -- I need approach..

    Desired output for ##1-4  

                


    <?xml version="1.0" encoding="UTF-8"?>
    <carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header xmlns="http://www.global.com/schema/common">
    <totalRecordCount xmlns="">3</totalRecordCount>
    <fileCreationDate xmlns="">2018-01-15</fileCreationDate>
    <correlationId xmlns="">CARIL0200115-1</correlationId>
    <senderId xmlns="">144R</senderId>
    <receiverId xmlns="">437</receiverId>
    </header>
    <carMaintenanceServiceStronglyTypedIBRequest>
    <createVendorRecordList>
      <recordCount>3</recordCount>
      <createVendorRecord>
        <recordId>1</recordId>
        <createVendor xmlns="http://mnc.com">
          <Vendor xmlns="">
            <orderStartDate>2016-01-01</orderStartDate>
            <Identifier>9876263</Identifier>
            <NId>N1234</NId>
            <primaryId>
              <identificationNumber>9876263</identificationNumber>
              <identificationType>
                <id>Highway part Company</id>
              </identificationType>
            </primaryId>
            <otherCorrespondenceAddressList operation="merge">
              <correspondenceInformation>
                <addressInformation>
                  <postalAddress>
                    <address>56 xyz Rd</address>
    <zip>56666</zip>
                  </postalAddress>
      <addressPhoneList>
                   <telephone>
                     <phoneNumber>31xx646</phoneNumber>
                     <phoneType>
                       <id>Mobile</id>
                     </phoneType>
                    </telephone>
                   <telephone>
                     <phoneNumber>31xx123</phoneNumber>
                     <phoneType>
                       <id>Office</id>
                     </phoneType>
                    </telephone>
                   </addressPhoneList>
                </addressInformation>
                <addressInformation>
                  <postalAddress>
                    <address>50 xyz Rd</address>
    <zip>54444</zip>
                  </postalAddress>
      <addressPhoneList>
                   <telephone>
                     <phoneNumber>31xx555</phoneNumber>
                     <phoneType>
                       <id>Mobile</id>
                     </phoneType>
                    </telephone>
                   </addressPhoneList>
                </addressInformation>
              </correspondenceInformation>
            </otherCorrespondenceAddressList>
          </Vendor>
          <asOfDate xmlns="">1900-01-01</asOfDate>
        </createVendor>
      </createVendorRecord>
      <createVendorRecord>
        <recordId>2</recordId>
        <createVendor xmlns="http://mnc.com">
          <Vendor xmlns="">
            <orderStartDate>2017-01-01</orderStartDate>
            <Identifier>9876264</Identifier>
            <NId>N1235</NId>
            <primaryId>
              <identificationNumber>9876264</identificationNumber>
              <identificationType>
                <id>Highway part Company_N</id>
              </identificationType>
            </primaryId>
            <otherCorrespondenceAddressList operation="merge">
              <correspondenceInformation>
                <addressInformation>
                  <postalAddress>
                    <address>45 xyz Rd</address>
                  </postalAddress>
      <addressPhoneList>
                   <telephone>
                     <phoneNumber>31xx222</phoneNumber>
                     <phoneType>
                       <id>Office</id>
                     </phoneType>
                    </telephone>
                   </addressPhoneList>
                </addressInformation>
              </correspondenceInformation>
            </otherCorrespondenceAddressList>
          </Vendor>
          <asOfDate xmlns="">1900-01-01</asOfDate>
        </createVendor>
      </createVendorRecord>  
      <createVendorRecord>
        <recordId>3</recordId>
        <createVendor xmlns="http://mnc.com">
          <Vendor xmlns="">
            <orderStartDate>2018-01-01</orderStartDate>
            <Identifier>9876265</Identifier>
            <NId>N1236</NId>
            <primaryId>
              <identificationNumber>9876265</identificationNumber>
              <identificationType>
                <id>ABC Corp</id>
              </identificationType>
            </primaryId>
            <otherCorrespondenceAddressList operation="merge">
              <correspondenceInformation>
                <addressInformation>
                  <postalAddress>
                    <address>10 xyz Rd</address>
                  </postalAddress>
                </addressInformation>
              </correspondenceInformation>
            </otherCorrespondenceAddressList>
          </Vendor>
          <asOfDate xmlns="">1900-01-01</asOfDate>
        </createVendor>
      </createVendorRecord>   
    </createVendorRecordList>
    </carMaintenanceServiceStronglyTypedIBRequest>
    </carMaintenanceServiceRequestRoot>

    @@version
    Microsoft SQL Server 2016 (SP2-CU13) (KB4549825) - 13.0.5820.21 (X64)

    T.I.A

    Friday, July 31, 2020 6:18 AM
  • Hi Yitzhak,

    With some modifications and Group By Clause my query is working with two issues left now..

    1. Xquery is not working
    2. If phone Elements are NULL then I am seeing tags

    Here is what I have done

    -- DDL and sample data population, start
    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (2, 'CARIL0200115-1', '144R', 437);

    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264'),
    ('9876265')

    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),
        Zip varchar(10),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd','56666', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd','56666', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 102, '50 xyz Rd','54444', '31xx555', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876264', 103, '45 xyz Rd', NULL,'31xx222', 'office', 'Highway part Company_N', '2017-01-01', 'N1235'),
    ('9876265', 104, '10 xyz Rd', NULL, NULL, NULL,'ABC Corp', '2018-01-01', 'N1236')


    -- DDL and sample data population, end

    -- just to see it
    --SELECT * FROM @headerdetails;
    --SELECT * FROM @v;
    --SELECT * FROM @vd;

    DECLARE @fileCreationDate DATE = GETDATE();

    -- real deal

    SELECT (
    SELECT NULL,
    (
    SELECT *
    FROM @headerdetails
    FOR XML PATH(''), TYPE, ROOT('header')
    ),
    (
    SELECT Identifier,
    (SELECT 
    (SELECT  add_id,[Other_Address],[Zip],orderStartDate,NId,
    (Select distinct [phoneNumber], [PhoneTypeid]
    FROM @vd vp
    where vd1.Identifier = vp.Identifier and vd1.add_id = vp.add_id
    FOR XML PATH('phone'), TYPE, ROOT('phones')
    )
    FROM (SELECT Identifier, add_id, [Other_Address],[Zip],orderStartDate,NId FROM @vd) vd1
    where vd1.Identifier = ad.Identifier and vd1.add_id = ad.add_id
    group by [Other_Address],Identifier, add_id,[Zip],orderStartDate,NId
    FOR XML PATH('postalAddress'), TYPE, ROOT('addressInformation')
    )
    FROM (SELECT DISTINCT Identifier, add_id FROM @vd) ad
    where v.Identifier = ad.Identifier
    FOR XML PATH(''), TYPE
    )
    FROM @v v
    FOR XML PATH('r'), TYPE, ROOT('vendors')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    )
    .query('<carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance">
    {
    for $h in /*:root/*:header
    return (<header xmlns="http://www.global.com/schema/common">
    <totalRecordCount>{data($h/*:totalRecordCount)}</totalRecordCount>
    <fileCreationDate>{sql:variable("@fileCreationDate")}</fileCreationDate>
    <correlationId>{data($h/*:correlationId)}</correlationId>
    <senderId>{data($h/*:senderid)}</senderId>
    <receiverId>{data($h/*:receiverId)}</receiverId>
    </header>,
    <carMaintenanceServiceStronglyTypedIBRequest>
    <createVendorRecordList>
    <recordCount>{count(/*:root/*:vendors/*:r)}</recordCount>
    {
    for $x in /*:root/*:vendors/*:r
    return <createVendorRecord>
    <recordId>{data($x/*:seq)}</recordId>
    <createVendor xmlns="http://mnc.com">
    <Vendor>
    <orderStartDate>{data($x/*:orderStartDate)}</orderStartDate>
    <Identifier>{data($x/*:Identifier)}</Identifier>
    <NId>{data($x/*:NId)}</NId>
    <primaryId>
    <identificationNumber>{data($x/*:Identifier)}</identificationNumber>
    <identificationType>
    <id>Highway part Company</id>
    </identificationType>
    </primaryId>
    <otherCorrespondenceAddressList operation="merge">
    <correspondenceInformation>
    <addressInformation>
    <postalAddress>
    <address>{data($x/*:other_Address)}</address>
    </postalAddress>
    <addressPhoneList>
    {
    for $ph in $x/*:phones/*:phone
    return
    <telephone>
    <phoneNumber>{data($ph/*:phoneNumber)}</phoneNumber>
    <phoneType>
    <id>{data($ph/*:PhoneTypeid)}</id>
    </phoneType>
    </telephone>
    }
    </addressPhoneList>
    </addressInformation>
    </correspondenceInformation>
    </otherCorrespondenceAddressList>
    </Vendor>
    <asOfDate>1900-01-01</asOfDate>
    </createVendor>
    </createVendorRecord>
    }
    </createVendorRecordList>
    </carMaintenanceServiceStronglyTypedIBRequest>
    )
    }
    </carMaintenanceServiceRequestRoot>');

    Please let me know how to fix.

    T.I.A



    • Edited by papillon28 Friday, July 31, 2020 2:06 PM
    Friday, July 31, 2020 1:53 PM
  • Hi papillon28,

    Please try the following solution.

    1. The raw XML is matching the requirements. Check it out
    2. NULL values for the ZIP and entire phone fragment are taken care.
    3. The final XML is matching  the desired output.


    SQL:

    -- DDL and sample data population, start
    -- header information table  Need this info at start of file output only once
    DECLARE @headerdetails TABLE
    (
        totalRecordCount INT,
        correlationId VARCHAR(255),
        senderid VARCHAR(255),
        receiverId INT
    );
    INSERT INTO @headerdetails VALUES
    (3, 'CARIL0200115-1', '144R', 437);
    
    -- vendor table unique Identifier records need to store 
    DECLARE @v TABLE (Identifier VARCHAR(100));
    INSERT INTO @v VALUES
    ('9876263'),
    ('9876264');
    
    -- vendor details table
    DECLARE @vd TABLE 
    (
        Identifier VARCHAR(100),
        add_id INT,
        other_Address VARCHAR(50),
    	Zip varchar(10),
        phoneNumber VARCHAR(100),
        PhoneTypeid VARCHAR(100),
        identificationType VARCHAR(255),
        orderStartDate VARCHAR(20),
        NId VARCHAR(15)
    );
    INSERT INTO @vd VALUES
    ('9876263', 101, '56 xyz Rd','56666', '31xx646', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 101, '56 xyz Rd','56666', '31xx123', 'office', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876263', 102, '50 xyz Rd','54444', '31xx555', 'Mobile', 'Highway part Company', '2016-01-01', 'N1234'),
    ('9876264', 103, '45 xyz Rd', NULL,   '31xx222', 'office', 'Highway part Company_N', '2017-01-01', 'N1235'),
    ('9876265', 104, '10 xyz Rd', NULL, NULL,  NULL, 'ABC Corp', '2018-01-01', 'N1236');
    -- DDL and sample data population, end
    
    -- just to see it
    SELECT * FROM @headerdetails;
    SELECT * FROM @v; 
    SELECT * FROM @vd;
    
    DECLARE @fileCreationDate DATE = GETDATE();
    
    -- real deal
    SELECT (
    SELECT -- create root tag on the fly
    (
    	SELECT *
    	FROM @headerdetails
    	FOR XML PATH(''), TYPE, ROOT('header')
    )
    , (
    	SELECT Identifier, identificationType, orderStartDate, NId
    		, ROW_NUMBER() OVER(ORDER BY Identifier) AS seq
    		, (
    			SELECT Identifier, add_id, other_Address, Zip
    				, (
    				SELECT add_id, phoneNumber, PhoneTypeid
    				FROM @vd AS ph
    				WHERE ad.add_id = ph.add_id
    				FOR XML PATH('phone'), TYPE, ROOT('phones')
    			)			
    			FROM (SELECT DISTINCT Identifier, add_id, other_Address, Zip FROM @vd) AS ad
    			WHERE ad.Identifier = vd.Identifier
    			FOR XML PATH('address'), TYPE, ROOT('addresses')
    		)
    	FROM (SELECT DISTINCT Identifier, identificationType, orderStartDate, NId
    	FROM @vd) AS vd
    	FOR XML PATH('r'), TYPE, ROOT('vendors')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<carMaintenanceServiceRequestRoot xmlns="http://www.global.com/schema/carmaintenance">
    {
    	for $h in /*:root/*:header
    	return (<header xmlns="http://www.global.com/schema/common">
    				<totalRecordCount>{data($h/*:totalRecordCount)}</totalRecordCount>
    				<fileCreationDate>{sql:variable("@fileCreationDate")}</fileCreationDate>
    				<correlationId>{data($h/*:correlationId)}</correlationId>
    				<senderId>{data($h/*:senderid)}</senderId>
    				<receiverId>{data($h/*:receiverId)}</receiverId>
    			</header>,
    			<carMaintenanceServiceStronglyTypedIBRequest>
    					<createVendorRecordList>
    						<recordCount>{count(/*:root/*:vendors/*:r)}</recordCount>
    						{
    							for $x in /*:root/*:vendors/*:r
    							return <createVendorRecord>
    										<recordId>{data($x/*:seq)}</recordId>
    										<createVendor xmlns="http://mnc.com">
    											<Vendor>
    												<orderStartDate>{data($x/*:orderStartDate)}</orderStartDate>
    												<Identifier>{data($x/*:Identifier)}</Identifier>
    												<NId>{data($x/*:NId)}</NId>
    												<primaryId>
    													<identificationNumber>{data($x/*:Identifier)}</identificationNumber>
    													<identificationType>
    														<id>Highway part Company</id>
    													</identificationType>
    												</primaryId>
    												<otherCorrespondenceAddressList operation="merge">
    													<correspondenceInformation>
    													{
    													for $ad in $x/*:addresses/*:address
    													return <addressInformation>
    															<postalAddress>
    																<address>{data($ad/*:other_Address)}</address>
    																{
    																if ($ad/*:Zip[string-length(text()[1]) gt 0]) then
    																	<zip>{data($ad/*:Zip)}</zip>
    																else ()
    																}
    															</postalAddress>
    															{
    															if ($ad/*:phones/*:phone/*:phoneNumber[string-length(text()[1]) gt 0]) then
    																<addressPhoneList>
    																{
    																for $ph in $ad/*:phones/*:phone
    																return
    																	<telephone>
    																		<phoneNumber>{data($ph/*:phoneNumber)}</phoneNumber>
    																		<phoneType>
    																			<id>{data($ph/*:PhoneTypeid)}</id>
    																		</phoneType>
    																	</telephone>
    																	}
    																</addressPhoneList>
    															else ()
    															}
    														</addressInformation>
    													}
    													</correspondenceInformation>
    												</otherCorrespondenceAddressList>
    											</Vendor>
    											<asOfDate>1900-01-01</asOfDate>
    										</createVendor>
    								</createVendorRecord>
    						}
    					</createVendorRecordList>
    				</carMaintenanceServiceStronglyTypedIBRequest>
    			)
    }
    </carMaintenanceServiceRequestRoot>');

    • Edited by Yitzhak Khabinsky Friday, July 31, 2020 2:18 PM
    • Marked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    • Unmarked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    • Marked as answer by papillon28 Friday, July 31, 2020 6:19 PM
    Friday, July 31, 2020 2:15 PM
  • Really it worked!! :) Thank you!
    Friday, July 31, 2020 6:18 PM