Show the self closing tag

Question

I have a query in SQL Server like:

SELECT   'ABC.COM'          AS  'CompanyName'
          GETDATE()         AS  'TimeOFGeneration'        
         'ABC'              AS  'Address',
(SELECT
       QUOTENAME(101,'"')   AS 'Version',
       QUOTENAME(101.1,'"') AS 'NewVersion',
       CAST(DATE AS DATE)   AS 'LAUNCHDATE'
       '365'                AS  'DURATION'
FROM TABLE
FOR XML PATH ('DATA'), TYPE , ROOT('SUBDATA')
)AS 'MAINDATA'

FOR XML PATH 'TIMELINE')

I am able to achieve the data in XML tags with this query, but how can I show the values for Version and NewVersion in self closed tags

<TIMELINE>
  <CompanyName>ABC.COM</CompanyName>
  <TimeOFGeneration>2023-10-18T09:09:05.540</TimeOFGeneration>
  <Address>ABC</Address>
  <MAINDATA>
    <SUBDATA>
      <DATA>
        <Version>"101"</Version>
        <NewVersion>"101.1"</NewVersion>
        <LAUNCHDATE>2023-10-18T05:00:00</LAUNCHDATE>
        <DURATION>365</DURATION>
      </DATA>
    </SUBDATA>
  </MAINDATA>
</TIMELINE>

But I need to display the version and NewVersion as

<TIMELINE>
  <CompanyName>ABC.COM</CompanyName>
  <TimeOFGeneration>2023-10-18T09:09:05.540</TimeOFGeneration>
  <Address>ABC</Address>
  <MAINDATA>
    <SUBDATA>
      <DATA>
        <Version="101"/>
        <NewVersion="101.1"/>
        <LAUNCHDATE>2023-10-18T05:00:00</LAUNCHDATE>
        <DURATION>365</DURATION>
      </DATA>
    </SUBDATA>
  </MAINDATA>
</TIMELINE>

Thanks in advance.

Answer 1

Using @ThomA's comment about your desired output not being valid XML as a springboard, I've specified an attribute of "number" for both the Version and NewVersion tags like so:

SELECT   
    'ABC.COM' AS  [CompanyName],
    GETDATE() AS  [TimeOFGeneration],     
    'ABC'     AS  [Address],
    (
        SELECT
            NULL as [Version],
            (SELECT 101 AS [@number] for xml path('Version'), type),

            NULL as [NewVersion],
            (SELECT 101.1 AS [@number] for xml path('NewVersion'), type),

            CAST(getdate() AS DATE)   AS [LAUNCHDATE],
            '365'                AS  [DURATION]
        FOR XML PATH ('DATA'), TYPE , ROOT('SUBDATA')

    ) AS [MAINDATA]
FOR XML PATH('TIMELINE')

By way of explanation, I've specified an empty tag for each of Version and NewVersion with a NULL column and then by following those immediately with a column called @number, that value gets added as a property to the preceding tag. It takes some reading of the docs to determine that that's the behavior.

Note - I had to remove the FROM TABLE from your example but it shouldn't change the technicals any.

Answer 2

It seems that the desired XML shall be composed without any database table involvement.

In such case it is very easy to compose XML "visually" via XQuery .query() method. The only dynamic XML elements, <TimeOFGeneration> and <LAUNCHDATE>, are coming from the T-SQL variable.

SQL

DECLARE @DateTimeOFGeneration DATETIMEOFFSET(3) = SYSDATETIMEOFFSET();
DECLARE @dt VARCHAR(30)=  FORMAT(@DateTimeOFGeneration, 'yyyy-MM-ddTHH:mm:ss.fffzzz');

SELECT CAST(N'' AS XML).query
('<TIMELINE>
    <CompanyName>ABC.COM</CompanyName>
    <TimeOFGeneration>{sql:variable("@dt")}</TimeOFGeneration>
    <Address>ABC</Address>
    <MAINDATA>
        <SUBDATA>
            <DATA>
                <Version number="101"/>
                <NewVersion number="101.1"/>
                <LAUNCHDATE>{sql:variable("@dt")}</LAUNCHDATE>
                <DURATION>365</DURATION>
            </DATA>
        </SUBDATA>
    </MAINDATA>
</TIMELINE>');

Output

<TIMELINE>
  <CompanyName>ABC.COM</CompanyName>
  <TimeOFGeneration>2023-10-18T18:53:25.861-04:00</TimeOFGeneration>
  <Address>ABC</Address>
  <MAINDATA>
    <SUBDATA>
      <DATA>
        <Version number="101" />
        <NewVersion number="101.1" />
        <LAUNCHDATE>2023-10-18T18:53:25.861-04:00</LAUNCHDATE>
        <DURATION>365</DURATION>
      </DATA>
    </SUBDATA>
  </MAINDATA>
</TIMELINE>