Need help to Add XML encode using T-SQL

Question

Need help to Add XML encode using T-SQL

gracebite

Right there with Babe

Points: 725
More actions
January 22, 2014 at 9:41 am

#281449

Hi All,
Can you please help me out on
How to add '<?xml version="1.0" encoding="UTF-8"?>'
To XML using T-sql
Thank You,
Grace

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

gracebite (1/22/2014)
Hi All,
Can you please help me out on
How to add '<?xml version="1.0" encoding="UTF-8"?>'
To XML using T-sql
Thank You,
Grace

Pretty sparse on details here. I am guessing at what you want to do but something like this should at least point you in the right direction.

declare @xml xml = '<asdf>woot</asdf>'

set @xml = '<?xml version="1.0" encoding="UTF-8"?>' + cast(@xml as varchar(max))

select @xml

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gracebite Right there with Babe Points: 725 More actions · Answer 2

getting with your query like

(No column name)

But I need like

<?xml version="1.0" encoding="UTF-8"?>

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

gracebite (1/22/2014)
getting with your query like
(No column name)
<asdf>woot</asdf>
But I need like
<?xml version="1.0" encoding="UTF-8"?>
<asdf>woot</asdf>

Still not totally sure what you are trying to do. I will take another shot in the dark.

declare @xml xml = '<asdf>woot</asdf>'

select '<?xml version="1.0" encoding="UTF-8"?>' + cast(@xml as varchar(max))

If you can explain what you are trying to do we can figure this out very easily.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gracebite Right there with Babe Points: 725 More actions · Answer 4

I am getting

Col

----

<?xml version="1.0" encoding="UTF-8"?><asdf>woot</asdf> in a string when I am trying to convert it into XML like select CONVERT(XML,'<?xml version="1.0" encoding="UTF-8"?>' + cast(@xml as varchar(max)))

Getting only

Col

----

What I am trying to do is when I click on <?xml version="1.0" encoding="UTF-8"?><asdf>woot</asdf>

It should open in XML format with encoding part.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

The XML that SQL forms won't have a header. What you do is produce your XML from your SELECT ... FOR XML <whatever> and dump that into a variable.

Then you append the header/footer pieces you need to that string, and return it to wherever you're sending it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

Hopefully you understand what Evil Craig was saying. To put it another way: you cannot click on the XML returned from a SQL query in SSMS and see a header when it is rendered in a new pane.

What I am trying to do is when I click on <?xml version="1.0" encoding="UTF-8"?><asdf>woot</asdf>
It should open in XML format with encoding part.

Why would you want to do this? What is confusing here is what benefit of seeing the encoding in while looking at it in SSMS? There are applications that require an XML declaration, if you were exporting the XML to something that required a declaration you could do something like this (taking the code Sean put together):

IF OBJECT_ID('tempdb..##x') IS NOT NULL DROP TABLE ##x;

declare @xml xml = '<asdf>woot</asdf>';

declare @xml_convert varchar(max) = '<?xml version="1.0" encoding="UTF-8"?>'+cast(@xml as varchar(max));

select @xml_convert AS x into ##x;

--SELECT * FROM ##x

EXEC master..xp_cmdshell 'bcp "SELECT x FROM ##x" QUERYOUT "C:\x.xml" -c -T'

Again, we need to better understand what you're trying to do.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001