XML output in SQL help needed

Hi I have the following code in SQL that outputs xml between 2 tables. The output is 90% correct but if possible add to the output and then also remove some of the output text.

I am not sure if SQL has the ability to code a type of a element in the output. Please see the code below with the output. Currently if possible I would like to make 2 changes to my current output. The changes are list at the end of the port



DECLARE @ID_Rechnung int = 1978,
    @XMLData xml;

WITH XMLNAMESPACES ('?xml version="1.0" encoding="UTF-8"?' as ext)

SELECT
    @XMLData = xmldat.xmldataCol 
FROM
(
SELECT (
       SELECT
            -- HIER XML Daten generieren
            [InvoiceHeader].[InvoiceDate]            AS 'invoice-date',
            ([InvoiceHeader].[InvoiceNumber])                           AS 'invoice-number',
       cast(replace([InvoiceHeader].[GrossValue],' ','') as decimal(18,2))                           AS 'gross-total',
        cast(replace([InvoiceHeader].[NetValue],' ','') as decimal(18,2))                           AS 'amount-due',
        [InvoiceHeader].[VatRate]                           AS 'tax-rate',
        cast(replace([InvoiceHeader].[VatValue],' ','') as decimal(18,2))                           AS 'tax-amount',
        [ImagePath] AS 'image-scan-url',
        [InvoiceType] AS 'document-type',
        [LegalEntityVATNo] AS  'account-type/id',
        [LegalEntityName] AS  'account-type/name',
        [SupplierCode] as 'supplier/number',
        [Currency] as 'currency/code',   

    (
        SELECT rtrim([InvoiceLines].[LineNumber]) AS [order-line-num]
            , [PONumber] as [po-number],
            CAST([InvoiceLines].[UnitPrice] AS decimal(18,2)) AS Price ,
            [Quantity] as quantity,
            [TaxAmount] as [tax-amount],
            [LineTotal] as [total],
            [Decsription] as description


        FROM [InvoiceLines] WHERE [InvoiceLines].[DOCID] = @id_Rechnung
        FOR XML PATH('Invoice-line'), ROOT('invoice-lines'), TYPE
    )
FROM [InvoiceHeader] 
WHERE [InvoiceHeader].[DOCID]  = @ID_Rechnung
FOR XML PATH(''), TYPE, ROOT('invoice-header')

) AS xmldataCol
) AS xmldat;

SELECT  @XMLData
.query('<invoice-header>




{
   for $x in /invoice-header/*[local-name()!="root"]
   return $x,
     for $x in /invoice-header/root/r
     return <invoice-lines>/<invoice-line>{$x/*}</invoice-line></invoice-lines>
}
</invoice-header>');

Output:

<invoice-header>
  <invoice-date>20180509</invoice-date>
  <invoice-number>1075440</invoice-number>
  <gross-total>1376.67</gross-total>
  <amount-due>1197.10</amount-due>
  <tax-rate>15.00%</tax-rate>
  <tax-amount>179.57</tax-amount>
  <image-scan-url>\\INTEL-SQL01\Attachment\2018-06-20\7e0dd165-81d6-445a-95d1-8aac686d44ed\f9a1179c-2a54-480e-b97a-ce6ac7327ae0.000</image-scan-url>
  <account-type>
    <id>4010112052</id>
    <name>CONSOLIDATEDPOWERPROJECTS</name>
  </account-type>
  <supplier>
    <number>12345</number>
  </supplier>
  <currency>
    <code>ZAR</code>
  </currency>
  <invoice-lines xmlns:ext="?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?">
    <Invoice-line>
      <order-line-num>4</order-line-num>
      <po-number>120934861</po-number>
      <Price>50.00</Price>
      <quantity>1.000000</quantity>
      <tax-amount>7.500000</tax-amount>
      <total>50.00</total>
      <description>Test1</description>
    </Invoice-line>
    <Invoice-line>
      <order-line-num>2</order-line-num>
      <po-number>120934861</po-number>
      <Price>10.00</Price>
      <quantity>2.000000</quantity>
      <tax-amount>4.500000</tax-amount>
      <total>20.00</total>
      <description>Test2</description>
    </Invoice-line>
  </invoice-lines>
</invoice-header>

1.How do I get rid of the following xmlns:ext="?xml version="1.0" encoding="UTF-8&in the line: "<invoice-lines xmlns:ext="?xml version="1.0" encoding="UTF-8"?">"

How would I code "<tax-amount>7.500000</tax-amount> to get the output": "<tax-amount type="decimal">7.500000</tax-amount>"

Solution

Without a minimal reproducible example it is not possible to give you a full working answer.

(1) As @JeroenMostert already pointed out, the

'?xml version="1.0" encoding="UTF-8"?'

is an XML prolog declaration. Just delete the following line:

WITH XMLNAMESPACES ('?xml version="1.0" encoding="UTF-8"?' as ext)

(2) Here is a conceptual example how to add an attribute to an XML element. What is important here is a sequential order of adding, i.e. attribute shall be first, element itself is 2nd.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [tax-amount] VARCHAR(20));
INSERT INTO @tbl ([tax-amount]) VALUES
(N'7.500000'),
(N'18.000000');

SELECT 
    'decimal' AS [tax-amount/@type]
    , [tax-amount]
FROM @tbl
FOR XML PATH('r'), TYPE, ROOT('root');

Output

<root>
  <r>
    <tax-amount type="decimal">7.500000</tax-amount>
  </r>
  <r>
    <tax-amount type="decimal">18.000000</tax-amount>
  </r>
</root>