XML from SQL through VBScript

raj_alapati · March 21st, 2007, 12:48 AM

Hello friends,

I have an SQL.for this SQL i want to generate an XML file.this XML should contain the data returned by the SQL .i am using VBScript to generate the XML .XML file is generated successfully and is opened in EXCEL but if the dataset returned by the SQL has special characters,it doesn't get opened in XML(XML File is generated successfully).the VBScript i am using to generate the XML file is

query = vQuery & " for XML Auto" '--the vquery contains the SQL

cmdStreamSQL.Open
cmdStreamSQL.Charset = "ascii"
cmdStreamSQL.Type = adTypeText
cmdStreamSQL.WriteText "<?xml version=""1.0"" encoding=""UTF-8""?>"
cmdStreamSQL.WriteText "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>"
cmdStreamSQL.WriteText query, adWriteChar
cmdStreamSQL.WriteText "</sql:query></ROOT>"
cmdStreamSQL.Position = 0

Set cmd.ActiveConnection = cn
cmd.CommandStream = cmdStreamSQL
cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
cmd.CommandTimeout = 0

cmdStreamResult.Open
cmd.Properties("OutPut Stream") = cmdStreamResult
-----------------------------------------------------------
and the Error message returned was

"XML parse error an invalid character was found in text content."

Please suggest me a way, how to resolve this issue.However i came to know it can be helpful if i use "Nested" instead of "Auto".but i am very beginner to XML ,i am unable to use this in right way.

I replaced this Auto with Nested .it gave" syntax error near nested error message" (In vbscript as well as SQL query analyzer).
pls help me out here how to use Nested ad well as suggestions

joefawcett · March 21st, 2007, 02:56 AM

I don't know what you mean by "special characters", can you give an example? It also seems odd that you are specifying "ascii" as the charset for the stream and then specifying UTF-8 in the XML declaration.

--

Joe (Microsoft MVP - XML)

raj_alapati · March 21st, 2007, 05:27 AM

Yes ,you are correct. i changed to ascii all the way.it doesn't work.i tried with utf-8 also.it gives the error," switch from current encoding to specified encoding not supported".i added code "cmdStreamSQL.Charset = "ascii"" .even though its not working
pls help.

joefawcett · March 21st, 2007, 06:14 AM

I would try setting it to UTF-16 in both instances, you still haven't said what you mean by "special characters".

--

Joe (Microsoft MVP - XML)

raj_alapati · March 21st, 2007, 06:31 AM

Special characters like degree(Â°),#,square root.we tried with utf-16 also . for this UTF-16 even the file is not able to open in Internet Explorer also,which can be in the case of UTF-8

joefawcett · March 21st, 2007, 06:50 AM

How are you getting from the stream to an actual file?

--

Joe (Microsoft MVP - XML)

raj_alapati · March 21st, 2007, 09:14 AM

cmdStreamResult.SaveToFile vOutputFilePath, adSaveCreateOverWrite

The problem is solved.
I replaced the code with
Set oTestStream =CreateObject("ADODB.Stream")

Set oTestConnection =CreateObject("ADODB.Connection")

Set oTestCommand =CreateObject("ADODB.Command")

oTestConnection.Open "Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB;Data Source=Ganga;Initial Catalog=JDE_development;Integrated Security=SSPI;uid=sa;pwd=rfid;persist security info=false;"

  Set oTestCommand.ActiveConnection = oTestConnection

  oTestCommand.Properties("ClientSideXML") = True

  oTestCommand.CommandText = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT top 100 * FROM proddta.F4801 FOR XML AUTO</sql:query></ROOT>"

  oTestStream.Open

  'oTestCommand.Properties("XML Root").Value = "ROOT"

  oTestCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"

  oTestCommand.Properties("Output Stream").Value = oTestStream

  oTestCommand.Properties("Output Encoding").Value = "UTF-8"

  oTestCommand.Execute , , 1024

wscript.Echo "<?xml version=""1.0"" encoding=""iso-8859-1""?>"
WScript.Echo oTestStream.ReadText

oTestStream.Close()

Thank you very much for hearing my pain and problems

raj_alapati · March 23rd, 2007, 06:03 AM

Hi joefawcett,

the problem is not solved.

the database contains the word "cancel-nd 50lue netwk cable f".because of the special character in the field "",even though the XML file is generated,it's unable to open in Excel saying "Invalid character found in text content". i modified the code

to

Const adExecuteStream=1024
Const adReadAll=-1
Const adSaveCreateOverWrite = 2

Set FSO=CreateObject("Scripting.filesystemObject")
Set oTestStream =CreateObject("ADODB.Stream")
Set oTestConnection =CreateObject("ADODB.Connection")
Set oTestCommand =CreateObject("ADODB.Command")

oTestConnection.Open "provider=SQLXMLOLEDB.4.0;data provider=SQLNCLI;data source=Ganga;initial catalog=JDE_Development;Integrated Security=SSPI ;"
set oTestCommand.ActiveConnection = oTestConnection
oTestCommand.Properties("ClientSideXML") = True
oTestCommand.CommandText = "SELECT Top 12170 * from proddta.F4801 for XML Auto"
oTestStream.Open
oTestCommand.Properties("Output Stream").Value = oTestStream
oTestCommand.Properties("xml root") = "root"
oTestCommand.Properties("Output Encoding").Value = "iso-8859-1"
oTestCommand.Execute , , adExecuteStream
oTestStream.Position = 0

wscript.echo "write start : " & Now()
oTestStream.Savetofile "C:\log.xml" ,adSaveCreateOverWrite
wscript.echo "write End : " & Now()

I feel there is something i need to add up to the code.
when i searched on net, i found the page
http://forums.microsoft.com/TechNet/...6671&SiteID=17 discussing about this. but i am not sure what to do.
please help me.please modify the code

Thank you
Raj Deep.A

tswaters · May 28th, 2007, 07:56 PM

You need to replace any invalid characters with the unicode equivelent... e.g. & should become #60;

Here's a few vbscript functions to do this:

Code:

    Function escapeMe( theString )
        theString = ReplaceANSIRange( 38, 38, theString ) ' &
        theString = ReplaceANSIRange( 60, 60, theString ) ' >
        theString = ReplaceANSIRange( 62, 62, theString ) ' <
        theString = ReplaceANSIRange( 128, 159, theString ) ' some special characters  (exclude chr(160) which is &nbsp;)
        theString = ReplaceANSIRange( 161, 255, theString ) ' more special characters
        escapeMe = theString
    End Function


    Function ReplaceANSIRange( theStart, theEnd, theString )
        For x = theStart to theEnd
            if instr( theString, chr(x) ) Then
                theString = replace( theString, chr(x), "#" + cstr(x) + ";" )
            End If
            Next
        ReplaceANSIRange = theString
    End Function

For this particular example, you'll probably need to grab whatever is in the database and run it through the "escapeMe()" function before outputting to XML... I'm not sure if this will really work for you because there's 128 instr calls for each escapeMe call, and if you're doing it for each of the "TOP 12170" -- that might take some time. You could always change the ANSI ranges to ones you'd expect -- usually it's < > and & that cause the screwups, but any special character can.

tswaters · May 28th, 2007, 07:59 PM

ha.. just goes to show how messed up everything is... this forum stripped out some of my ampersands.

Quote:

quote:
e.g. & should become [ampersand]#60;

Quote:

quote:
theString = replace( theString, chr(x), "[ampersand]#" + cstr(x) + ";" )