XML declaration with "FOR XML PATH" in SQL Server 2005

Question

Below is a simplified version of a query that I have already created. The query works fine, but I cannot figure out how to get the XML declaration at the top of the generated XML. I've tried multiple things and searched far and wide on the Google, but alas I cannot seem to find out how to do this ... or even if it is possible.

select 
    'Dimension' "@type",
    (
        select
            (
                select
                    'X102' "TransactionType",
                    convert(varchar, getdate(), 104) "Transfer/TransferDate",
                    convert(varchar, getdate(), 108) "Transfer/TransferTime"
                for xml path (''), type
            ) "TransactionInformation"
        for xml path (''), type
    )
for xml path ('DimensionImport'), type

Gives me...

<DimensionImport type="Dimension">
    <TransactionInformation>
        <TransactionType>X102</TransactionType>
        <Transfer>
            <TransferDate>21.01.2010</TransferDate>
            <TransferTime>15:46:36</TransferTime>
        </Transfer>
    </TransactionInformation>
</DimensionImport>

I'm wanting...

<?xml version="1.0" encoding="ISO-8859-1" ?>
<DimensionImport type="Dimension">
    <TransactionInformation>
        <TransactionType>X102</TransactionType>
        <Transfer>
            <TransferDate>21.01.2010</TransferDate>
            <TransferTime>15:46:36</TransferTime>
        </Transfer>
    </TransactionInformation>
</DimensionImport>

Thank you in advance for any help you might be able to lend.

Solution 1

Here is what I ended up doing. The data type is not returned as an XML datatype, but I can deal with that I guess.

select '<?xml version="1.0" encoding="ISO-8859-1" ?>' +
(
    select 
        'Dimension' "@type",
        (
            select
                (
                    select
                        'X102' "TransactionType",
                        convert(varchar, getdate(), 104) "Transfer/TransferDate",
                        convert(varchar, getdate(), 108) "Transfer/TransferTime"
                    for xml path (''), type
                )
            for xml path ('TransactionInformation'), type
        ),
        (
            ... queried up data here ...
        )
    for xml path ('DimensionImport')
)

Solution 2

It's messy, but you could just concatenate it on the front...

SELECT '<? xml...>' + 
(select 
    'Dimension' "@type",
    (
        select
            (
                select
                    'X102' "TransactionType",
                    convert(varchar, getdate(), 104) "Transfer/TransferDate",
                    convert(varchar, getdate(), 108) "Transfer/TransferTime"
                for xml path (''), type
            ) "TransactionInformation"
        for xml path (''), type
    )
for xml path ('DimensionImport'), type)

Solution 3

try this:

select '<?xml version="1.0" encoding="ISO-8859-1" ?>' + 
       (your whole upper select here)

Solution 4

Unfortunately this is what I found in SQL Server Books Online:

The XML declaration PI in an instance is not preserved when the instance is stored in the database. For example:

Copy Code CREATE TABLE T1 (Col1 int primary key, Col2 xml)
GO
INSERT INTO T1 values (1, '<?xml version="1.0" encoding="windows-1252" ?><doc></doc>')
GO
SELECT Col2
FROM T1

The result is <doc/>.

The only workaround is to return the XML as a varchar(max) type:

select '<?xml version="1.0" encoding="ISO-8859-1" ?>'
+
cast( (
select 
    'Dimension' "@type",
    (
        select
            (
                select
                    'X102' "TransactionType",
                    convert(varchar, getdate(), 104) "Transfer/TransferDate",
                    convert(varchar, getdate(), 108) "Transfer/TransferTime"
                for xml path (''), type
            ) "TransactionInformation"
        for xml path (''), type
    )
for xml path ('DimensionImport'), type) as varchar(max))

View more solutions

Comments

[Davin Studer]

Alas, that did not work. Error returned: The data types varchar and xml are incompatible in the add operator.

[Davin Studer]

Error returned: The data types varchar and xml are incompatible in the add operator.

[Rob Farley]

Yes... if you remove your 'type', and then cast the whole result as xml, that'll help. Like this: `SELECT cast('<? xml...>' + (select 'Dimension' as [@type]....for xml path('DimensionImport')) as xml)

[Davin Studer]

Hmmm ... it runs if I remove the character encoding in the xml declaration, but not if I have it in there. With the character encoding in the declaration I get this error "XML parsing: line 1, character 39, unable to switch the encoding". When I remove the character encoding it runs, but I still do not have the xml declaration appended to the xml. It only shows the original xml. Ugh!!

[Davin Studer]

That is basically what I ended up doing. Thanks.