SQL Query output to XML ISO 8859

Question

Post reply

SQL Query output to XML ISO 8859

Denis Wilkinson

Mr or Mrs. 500

Points: 569
More actions
January 4, 2008 at 6:52 am

#62764

Hi,
I have a customer that I need to send an XML file to in ISO 8859 format with the results of a SQL query. Now while the code FOR XML AUTO, XMLDATA works it does not produce the output in ISO 8859 format. I know I may be clutching at straws but is there anyway to specify/config the XML file format in SQL?
I know I could do it with a stored proc; creating a string, documenting the XML structure and inputting the data where required in the structure. Then send using xp_sendmail. But that seems the wrong way to do it as should the structure need modifying it will need to be done in the code.
Any suggestions?
rgds
denis

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

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

David.Poole SSC Guru Points: 76035 More actions · Answer 1

ISO8859 seems to refer to Latin character sets.

Can you give a specific example of what is going wrong?

I use XML EXPLICIT when I need a specifc layout but only when I am feeling particularly masochistic.

To be honest, I tend to cop out of any XML work in the database and get the database to produce whatever it will then use an XSLT to transform the output into whatever I need.

LinkedIn Profile

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 2

Here is the format that the customer is requesting. Basically consists of the header info and then the data as a dataset nothing complex. I am not an expert in XML although I understand the structure and the data but not how or what formats it to a particular structure as below:

Sample XML File:

2005-12

-25 23:59:59

Regards

Denis

BOR15K SSCertifiable Points: 5894 More actions · Answer 3

Denis,

Did you get any help with ISO-8859?

I need to export an XML in Spanish as well.

Kind regards!

Bart Verlaan SSC Journeyman Points: 78 More actions · Answer 4

I’ve spent quit a few hours studying something comparable. I ended up writing an vb.net assembly. This assembly accepts an xml variable and writes a windows-1252 encoded xml file to disk. The xml variable is the result of a ‘FOR XML PATH’ query in a stored procedure. In SQL Server 2005 an xml variable is utf-16 by default, if i remember it right.

Here’s the part of my vb code doing the encoding and saving it to disk

Dim objXmlDoc As New XmlDocument()

Dim objXmlElement As XmlElement

Dim objXmlDecl As XmlDeclaration

objXmlDecl = objXmlDoc.CreateXmlDeclaration("1.0", Nothing, Nothing)

objXmlDecl.Encoding = "windows-1252"

objXmlDecl.Standalone = Nothing

objXmlDoc.InsertBefore(objXmlDecl, objXmlElement)

objXmlDoc.Save(Filename.Value)

Would this be of any help?

Denis Wilkinson Mr or Mrs. 500 Points: 569 More actions · Answer 5

Thanks for all the feedback, I actually got it done by creating a flat text result from a stored proc. Although it was not XML generated it was a file structured as XML and works well.

Thanks for the effort.

rgds