Post reply

SQL Query output to XML ISO 8859

  • 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

  • January 8, 2008 at 12:58 pm

    #765883

    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

  • January 9, 2008 at 3:06 am

    #766070

    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

  • June 15, 2008 at 12:50 pm

    #828998

    Denis,

    Did you get any help with ISO-8859?

    I need to export an XML in Spanish as well.

    Kind regards!

  • June 16, 2008 at 2:08 pm

    #829478

    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?

  • June 17, 2008 at 7:21 am

    #829763

    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

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

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