How to add attributes to the root element when generating XML?

[PPettitDN]

I'm experimenting with creating PIDX XML data straight from SQL (Microsoft SQL Server 2005 - 9.00.5057.00). I'm just getting started but ran into a roadblock when trying to add an attribute to the root element. This is what I've got so far:

DECLARE
	@xmlDoc VARCHAR(MAX)
	,@declaration VARCHAR(MAX)
	,@Invoice VARCHAR(MAX)
	,@InvoiceProperties VARCHAR(MAX)
	,@InvoiceDetails VARCHAR(MAX)
	,@InvoiceSummary VARCHAR(MAX)
	,@inv_num VARCHAR(12)
	,@inv_date VARCHAR(10)
	,@invoiceTotal DECIMAL(21,6)

SELECT
	@inv_num = LTRIM(RTRIM(inv_num))
	,@inv_date = CONVERT(VARCHAR(10),inv_date,126)
	,@invoiceTotal = price
FROM inv_hdr
WHERE
	inv_num = '     1445171'

SELECT
	@declaration = '<?xml version="1.0" encoding="UTF-8"?>'

;WITH XMLNAMESPACES ('[URL unfurl="true"]http://www.pidx.org/schemas/v1.61'[/URL] as pidx)
SELECT @xmlDoc = 
	(SELECT @declaration + CAST
		(
			(
				SELECT
					@InvoiceProperties AS [pidx:InvoiceProperties]
					,@inv_num AS [pidx:InvoiceProperties/pidx:InvoiceNumber]
					,@inv_date AS [pidx:InvoiceProperties/pidx:InvoiceDate]
					,'test' AS [pidx:InvoiceDetails/@testAttribute]
					,@InvoiceDetails AS [pidx:InvoiceDetails]
					,@InvoiceSummary AS [pidx:InvoiceSummary]
					,@invoiceTotal AS [pidx:InvoiceSummary/pidx:InvoiceTotal]
				FOR XML PATH (''), ROOT ('pidx:Invoice')
			)
			AS VARCHAR(MAX)
		)
	)
SELECT @xmlDoc

It generates this:

<?xml version="1.0" encoding="UTF-8"?>
<pidx:Invoice xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61">[/URL]
  <pidx:InvoiceProperties>
    <pidx:InvoiceNumber>1445171</pidx:InvoiceNumber>
    <pidx:InvoiceDate>2018-02-08</pidx:InvoiceDate>
  </pidx:InvoiceProperties>
  <pidx:InvoiceDetails testAttribute="test"/>
  <pidx:InvoiceSummary>
    <pidx:InvoiceTotal>9913.730000</pidx:InvoiceTotal>
  </pidx:InvoiceSummary>
</pidx:Invoice>

How would I go about adding these attributes to the root (Invoice) element?:
[ul]
[li]pidx:transactionPurposeIndicator="Original"[/li]
[li]pidx:version="1.61"[/li]
[/ul]

It should look like this:

...
<pidx:Invoice xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL] [highlight]pidx:transactionPurposeIndicator="Original"[/highlight] [highlight]pidx:version="1.61"[/highlight]>
...

 

[PPettitDN]

In case anyone is interested, below is a solution that is working as desired. I had to do a little messing around with the namespace reference at the very end because SQL requires the namespace while building the XML, but was adding it to more elements than just the root.

DECLARE
	@xmlDoc VARCHAR(MAX)
	,@declaration VARCHAR(MAX)
	,@inv_num VARCHAR(12)
	,@inv_date VARCHAR(10)
	,@invoiceTotal DECIMAL(21,6)

SELECT
	@inv_num = LTRIM(RTRIM(inv_num))
	,@inv_date = CONVERT(VARCHAR(10),inv_date,126)
	,@invoiceTotal = price
FROM inv_hdr
WHERE
	inv_num = '     1445171'

SELECT
	@declaration = '<?xml version="1.0" encoding="UTF-8"?>'

;WITH XMLNAMESPACES ('[URL unfurl="true"]http://www.pidx.org/schemas/v1.61'[/URL] as pidx)
SELECT @xmlDoc = 
	(SELECT @declaration + 
		CAST 
		(
			(SELECT
				'Original' AS [@pidx:transactionPurposeIndicator]
				,'1.61' AS [@pidx:version]
					,(SELECT CAST(
						(SELECT 
							@inv_num AS [pidx:InvoiceNumber]
							,@inv_date AS [pidx:InvoiceDate]
						FOR XML PATH('pidx:InvoiceProperties'))
					AS XML)
					)
					,(SELECT CAST(
						(SELECT 
							'lineitems' [pidx:LineItems]
						FOR XML PATH('pidx:InvoiceDetails'))
					AS XML)
					)
					,(SELECT CAST(
						(SELECT 
							@invoiceTotal AS [pidx:InvoiceTotal]
						FOR XML PATH('pidx:InvoiceSummary'))
					AS XML)
					)
				FOR XML PATH ('pidx:Invoice')
			) AS VARCHAR(MAX)
		)
	)

--Must keep the ";WITH XMLNAMSPACES..." reference while building the XML or there will be errors 
--Example:  XML name space prefix 'pidx' declaration is missing for FOR XML row name 'pidx:InvoiceProperties'.

--Get rid of ALL namespace references after the xml is built
SET @xmlDoc = REPLACE(@xmlDoc,'xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"','')[/URL]
--Add back just the first namespace reference
SET @xmlDoc = REPLACE(@xmlDoc,'<pidx:Invoice ','<pidx:Invoice xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"')[/URL]

SELECT @xmlDoc

Without the final string manipulation:

<?xml version="1.0" encoding="UTF-8"?>
<pidx:Invoice [highlight]xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL][/highlight] pidx:transactionPurposeIndicator="Original" pidx:version="1.61">
  <pidx:InvoiceProperties [highlight]xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL][/highlight]>
    <pidx:InvoiceNumber>1445171</pidx:InvoiceNumber>
    <pidx:InvoiceDate>2018-02-08</pidx:InvoiceDate>
  </pidx:InvoiceProperties>
  <pidx:InvoiceDetails [highlight]xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL][/highlight]>
    <pidx:LineItems>lineitems</pidx:LineItems>
  </pidx:InvoiceDetails>
  <pidx:InvoiceSummary [highlight]xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL][/highlight]>
    <pidx:InvoiceTotal>9913.730000</pidx:InvoiceTotal>
  </pidx:InvoiceSummary>
</pidx:Invoice>

With the string manipulation:

<?xml version="1.0" encoding="UTF-8"?>
<pidx:Invoice [highlight]xmlns:pidx="[URL unfurl="true"]http://www.pidx.org/schemas/v1.61"[/URL][/highlight] pidx:transactionPurposeIndicator="Original" pidx:version="1.61">
  <pidx:InvoiceProperties >
    <pidx:InvoiceNumber>1445171</pidx:InvoiceNumber>
    <pidx:InvoiceDate>2018-02-08</pidx:InvoiceDate>
  </pidx:InvoiceProperties>
  <pidx:InvoiceDetails >
    <pidx:LineItems>lineitems</pidx:LineItems>
  </pidx:InvoiceDetails>
  <pidx:InvoiceSummary >
    <pidx:InvoiceTotal>9913.730000</pidx:InvoiceTotal>
  </pidx:InvoiceSummary>
</pidx:Invoice>