Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XML
| Search | Today's Posts | Mark Forums Read
XML General XML discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XML section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 21st, 2007, 12:48 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default XML from SQL through VBScript

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

  #2 (permalink)  
Old March 21st, 2007, 02:56 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
  #3 (permalink)  
Old March 21st, 2007, 05:27 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old March 21st, 2007, 06:14 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
  #5 (permalink)  
Old March 21st, 2007, 06:31 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old March 21st, 2007, 06:50 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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

--

Joe (Microsoft MVP - XML)
  #7 (permalink)  
Old March 21st, 2007, 09:14 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #8 (permalink)  
Old March 23rd, 2007, 06:03 AM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi joefawcett,

the problem is not solved.

the database contains the word "cancel-nd 50 lue 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

  #9 (permalink)  
Old May 28th, 2007, 07:56 PM
Registered User
Points: 11, Level: 1
Points: 11, Level: 1 Points: 11, Level: 1 Points: 11, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #10 (permalink)  
Old May 28th, 2007, 07:59 PM
Registered User
Points: 11, Level: 1
Points: 11, Level: 1 Points: 11, Level: 1 Points: 11, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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) + ";" )




Similar Threads
Thread Thread Starter Forum Replies Last Post
Relational SQL Data to XML - Vet SQL/ASP - New XML JimiTheJett XML 1 December 4th, 2008 06:06 PM
VBScript or SQL solution to problem mat41 Classic ASP Professional 5 October 8th, 2008 05:13 PM
Create XML file from SLQ Server2000 using vbScript ilearn XML 1 September 5th, 2008 02:32 AM
Getting an average using SQL vs VBScript mat41 Classic ASP Professional 1 November 1st, 2007 06:33 PM
Create a new XML file with vbscript sswingle XML 4 April 24th, 2006 07:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.