Generating XML document using VB script 1

[lavadan]

Hi There,

I am newbie to VB Script. I need to generate the following XML document using VB script. Can you please advice me on this. I can manage atleast if i know the concept of generating XML using VB script.

<?xml version='1.0"?>
<authors>
<author>
<firstname>john</firstname>
<lastname>norton</lastname>
<publishingcompany>westwind inc</publishingcompany>
<year>2007</year>

In the above xml document, i will get the author name from a text file(about 10000). The publishing company and the year are the same for all the authors.

I can do this using sql server but we dont have a table containing author information and we have only read database. So can i generate this XML document using VB script. Please share your ideas.

 

[tsuji]

[1] Just mimic what you would do on your notepad. Suppose you have the data source in a csv (d:\data\authors.csv hereinbelow) and want to generate a xml file (d:\data\authors.xml), you can do this.
[tt]
'given here
dim src,out
'source in csv format (firstname,lastname) with no header
src="d:\data\authors.csv"
out="d:\data\authors.xml"

'main functional block here
dim fso,ots,sdata,adata,sxml
set fso=createobject("scripting.filesystemobject")
set ots=fso.opentextfile(src,1,false,-2)

sxml="<?xml version=""1.0"" encoding=""UTF-8"" ?>" & vbcrlf
sxml=sxml & "<authors>" & vbcrlf
do while not ots.atendofstream
sdata=ots.readline
adata=split(sdata,",")
sxml=sxml & vbtab & "<author>" & vbcrlf
sxml=sxml & vbtab & vbtab & gensimpletype("firstname",adata(0)) & vbcrlf
sxml=sxml & vbtab & vbtab & gensimpletype("lastname",adata(1)) & vbcrlf
sxml=sxml & vbtab & vbtab & gensimpletype("publishingcompany","westwind inc") & vbcrlf
sxml=sxml & vbtab & vbtab & gensimpletype("year","2007") & vbcrlf
sxml=sxml & vbtab & "</author>" & vbcrlf
loop
sxml=sxml & "</authors>"

ots.close

set ots=fso.opentextfile(out,2,true,-2)
ots.write sxml
ots.close

set ots=nothing
set fso=nothing
'wscript.echo "done"

function gensimpletype(stag,stext)
gensimpletype="<" & stag & ">" & stext & "</" & stag & ">"
end function
[/tt]
[2] You just have to make sure the csv (with format firstname,lastname) is proper and does not contain blank line etc... as no error trapping is scripted for simplicity and illustration of the principle.

[3] You've probably in mind some sophisticated method; but, for 10^4 data, you're better of with plain text file generation.

 

[tsuji]

[4] If the format is (lastname,firstname) which probably is opted more often, just swap the place of adata(0) and adata(1).

 

[lavadan]

Hi tsuji,

Thanks a lot for this code. It perfectly works.
Sure i will get back to you if i have any questions.

Thanks much,
Lavadan

 

[lavadan]

Hi,

I have one more question.Suppose i want the xml document as below

<?xml version='1.0"?>
<authors>
<author>
<firstname>john</firstname>
<lastname>norton</lastname>
<publishingcompany>westwind inc</publishingcompany>
<year>2007</year>
<folder id=" Authors of 2007" folder_type_id=65"/>
<authors>
<author>

for each author, the folder id and folder_type_id are same.
In this case, i modified the VB script to:

sxml=sxml & vbtab & vbtab & gensimpletype("publishingcompany","westwind inc") & vbcrlf
sxml=sxml & vbtab & vbtab & gensimpletype("year","2007") & vbcrlf
sxml=sxml & vbtab & "<folder_id= "authors of 2007" folder_type_id=65/>" & vbcrlf
sxml=sxml & vbtab & "</author>" & vbcrlf

I know this wrong and i got an error. How do i display a string within inverted commas in VB script,so that i would have the xml line as :

<folder id=" Authors of 2007" folder_type_id=65"/>

Tsuji can you please guide me with this

 

[dm4ever]

<folder_id= ""authors of 2007"" folder_type_id=65/>"

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[tsuji]

>sxml=sxml & vbtab & "<folder_id= "authors of 2007" folder_type_id=65/>" & vbcrlf

Ditto to the idea of what shown. But since there should be some conceptual issues in the original in the sense that, as I see it, there are meant to be two attributes(?), id and folder_type_id. If that is what meant to be, there should not be the underscore for folder tag name and id attribute name. Secondly, every value in an attribute must be quoted, hence, 65 should be appeared quoted. This is the correct line.

[tt]sxml=sxml & vbtab & vbtab & "<folder id=""Authors of 2007"" folder_type_id=""65"" />" & vbcrlf[/tt]

 

[lavadan]

Thanks all. It worked.

 

[lavadan]

Hi tsuji and dm4ever,

I have one more challenge. Can you please help me with this.

'given here
dim src,out
'source in csv format (firstname,lastname) with no header
src="d:\data\authors.csv"
out="d:\data\authors.xml"

In the above piece of script,the path to the csv file differs from one server to another. So i was planning to create a text file which has the path to the csv file. So whenver the path changes, the team only needs to change the text file without touching the script.

How would src read a txt file and get the path to csv file

One more question if you guys dont mind,

If the authors.xml file already exists, then the script should delete the existing authorsxml file and create a new one. How can i do this?

Pleas ehelp me with this!

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/z9ty6h50.aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

[5]
>If the authors.xml file already exists, then the script should delete the existing authorsxml file and create a new one. How can i do this?
It is already, is it not?
[6]
So i was planning to create a text file which has the path to the csv file.
You can use the fso already demonstrated its use in [1]. But since in [1] I conciously separate some functionality that one can easily build a sub out of it, I would suggest this slightly sub-optimal approach by creating fso independently (otherwise you just have to move some lines up for the purpose).
[tt]
'given
dim srccsv,src,out,fso
srccsv="d:\data\csvlocation.txt"
src=""
out="d:\data\authors.xml"
set fso=createobject("scripting.filesystemobject")
if not fso.fileexists(srccsv) then
wscript.echo "The file containing the csv path does not exists. Operation will be aborted."
else
src=fso.opentextfile(srccsv,1,false,-2).readline
end if
if src="" or (not fso.fileexists(src)) then
wscript.echo "Cannot find the source csv file. Operation aborted."
wscript.quit
end if
set fso=nothing 'just want a clear break: not really necessary.
'continue with the rest
[/tt]

 

[lavadan]

Thanks tsuji. It worked.

I have one more question.

As soon as i execute the script, i want my script to read the path where it is stored.

For example if the script is saved at "c:/test",
the script should retrieve this path(c:/test) and write
srccsv="c:/test"+"csvlocation.txt"

Can you please help me with this too!

 

[tsuji]

[tt]spath=replace(wscript.scriptfullname,wscript.scriptname,"")[/tt]

 

[lavadan]

Tsuji,

I have one more question.

I have a text file authorinfo.text. The first line of the text file has a path c:\somefile.txt and the second line has a server name(11.12.1645).Now the script has to read the authorinfo.text file and assign src=c:\somefile.txt and src1=\\11.12.1645.

i.e the script should assign the first line in authorinfo.txt to src and the second line to src1.

How can i do this VB script. Please help me with this as i am new to vb script.

Thanks.

 

[EBGreen]

The thread that just keeps on giving. Look at the documentation for the FileSystemObject. Specifically the .OpenTextFile method. And the File Object's .ReadLine and .ReadAll methods. Searching this forum for those keywords will also get you plenty of samples of reading data from files.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]