Adding XML version tag

_{Monday, March 19, 2018 6:11 AM}

I have an xml query with me as shown below.It generates output properly.
Now a change needs to be made to the query for adding the version as  <?xml version="1.0"?>  at the beginning of the xml.

select top 10
tasmanID '@id'
, [SupporterType] '@SupporterType'
, createnew '@createNew'
, (
select 
i.CREATEDDATE
, i.time
, i.channel
,i.SupporterType_Category
,i.comments
,i.TASKSUBTYPE
from #test i
where i.tasmanID = s.tasmanID
for xml path('Interaction'), type
)
from #test s
for xml path('tasmanID'), type, root('xml')

Can anyone help me to add the same with this query ?

_{Monday, March 19, 2018 7:04 AM ✅Answered}

you've to do something like this

select '<?xml version="1.0"?>' + 
(select top 10
tasmanID '@id'
, [SupporterType] '@SupporterType'
, createnew '@createNew'
, (
select 
i.CREATEDDATE
, i.time
, i.channel
,i.SupporterType_Category
,i.comments
,i.TASKSUBTYPE
from #test i
where i.tasmanID = s.tasmanID
for xml path('Interaction'), type
)
from #test s
for xml path('tasmanID'), type, root('xml'))

Keep in mind the datatype will be changed to nvarchar(max) in that case

If you cast it to xml it will lose the version part as version info is not preserved within the xml value in t-sql. Those are just processing instructions

Another method is to append this info outside SQL if you're generating XML file. This can be done very easily using DOS copy command

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Tuesday, March 20, 2018 12:01 AM}

Thanks for the reply.However this query gives me error as

"The data types varchar and xml are incompatible in the add operator."

_{Tuesday, March 20, 2018 1:34 PM}

Hi Ramesh,

The line you are trying to add to the XML is called a prolog:

Prolog [XML Standards]

SQL:

DECLARE @tbl TABLE(
    ID INT
    , City VARCHAR(20)
);
INSERT INTO @tbl (ID, City)
VALUES (0, 'Miami')
, (1, 'Orlando');

-- test
SELECT * FROM @tbl FOR XML PATH('Location'), TYPE, ROOT('root');

-- final output, SELECT is copied from the line above
SELECT '<?xml version="1.0" encoding="UTF-8"?>' + 
CAST((SELECT * FROM @tbl FOR XML PATH('Location'), TYPE, ROOT('root')) AS VARCHAR(MAX)) AS XmlData

Output, as mentioned by Visakh46, is a VARCHAR(MAX) data type.

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <Location>
        <ID>0</ID>
        <City>Miami</City>
    </Location>
    <Location>
        <ID>1</ID>
        <City>Orlando</City>
    </Location>
</root>

_{Tuesday, March 20, 2018 1:41 PM}

Thanks for the reply.However this query gives me error as

"The data types varchar and xml are incompatible in the add operator."

just cast it to varchar

select '<?xml version="1.0"?>' + 
cast((select top 10
tasmanID '@id'
, [SupporterType] '@SupporterType'
, createnew '@createNew'
, (
select 
i.CREATEDDATE
, i.time
, i.channel
,i.SupporterType_Category
,i.comments
,i.TASKSUBTYPE
from #test i
where i.tasmanID = s.tasmanID
for xml path('Interaction'), type
)
from #test s
for xml path('tasmanID'), type, root('xml'))
as varchar(max))

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page