Solved

Write XML file using VBScript

Posted on 2003-10-27
11
4,138 Views
Last Modified: 2013-11-19
Dear All,
How can write XML file ( "attributes" format only ) using msxml + VBScript driven from DB.
Full code example will be appreciated.
Thanks.

0
Comment
Question by:ethar1
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 21

Assisted Solution

by:ap_sajith
ap_sajith earned 300 total points
ID: 9626494
0
 
LVL 21

Assisted Solution

by:ap_sajith
ap_sajith earned 300 total points
ID: 9626538
0
 
LVL 2

Assisted Solution

by:hb21l6
hb21l6 earned 100 total points
ID: 9626645
here you go!!!!

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

  Sub Page_Load(Sender As Object, E As EventArgs)

    Dim strConnection As String
    Dim strSQL        As String
    Dim objDataSet    As New DataSet()
    Dim objConnection As OleDbConnection
    Dim objAdapter    As OleDbDataAdapter

    ' set the connection and query details
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "Data Source=" & Server.MapPath("db1.mdb")
    strSQL = "SELECT count(field1) as field1, field2 FROM table group by filed2 order by field2;"

    ' open the connection and set the command
    objConnection = New OledbConnection(strConnection)
    objAdapter = New OledbDataAdapter(strSQL, objConnection)

    ' fill the dataset with the data
    objAdapter.Fill(objDataSet, "Employees")

       'tells it where to store the info from the table you selected above
    objDataSet.WriteXml(Server.MapPath("chartme.xml"))
   
       'gives you a button to view the xml file you just created
    Response.Write("<a href='chartme.xml'>View XML file</a>")

  End Sub

</script>

let us know how you get on

hb

0
 

Author Comment

by:ethar1
ID: 9626699
hb21l6
can you please elaborate a bit?
when I try to execute this vbs using : cscript s.vbs, it give an error :
H:\Web\s.vbs(1, 1) Microsoft VBScript compilation error: Expected statement

I change the strConnection to the appropriate one.
strSQL = "Select * from dailyreport"


what is this :
objAdapter.Fill(objDataSet, "Employees")

Thanks.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 9626715
please provide your sql statement or fields to be included in the xml file and your db table(s) list of fields
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ethar1
ID: 9626735
   strSQL = "Select ReportID, PersonalContact , UserFav from dailyreport"
0
 
LVL 35

Accepted Solution

by:
YZlat earned 100 total points
ID: 9627021
Create xsl file. name it report.xsl

report.xsl file content:

<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
  <xsl:template match="/">
  <HTML>
<STYLE>
    .fr1  { width: 30em; }
    BODY       { margin:0px; width: 30em;
                 font-family: Arial, Helvetica, sans-serif; font-size: smaller;}
    P          { margin-top: .5em; margin-bottom: .25em; }
    HR         { color: #888888; }
    .H1        { color: #660033; font-weight: bold; vertical-align: top; }
    .Param     { font-size: smaller; vertical-align: top; }
    .tagline   { font-style: italic; font-size: smaller; text-align: right; }
    .body      { text-align: justify; background-color: linen; }
    .dingbat   { font-family: WingDings; font-style: normal; font-size: xx-small; }
    .person    { font-weight: bold; }
    .label     { font-weight: bold; }
    .self      { font-style: italic; font-size: smaller;}
    #menu      { border: 2px solid black; padding: 1em; background-color: #888833; }
    .menutext  { color: #FFFFDD; font-family: Times, serif; font-style: italic;
                 vertical-align: top; text-align:center; }
    .menuhead  { color: #FFFFDD; font-family: Times, serif; font-weight: bold;
                 vertical-align: top; text-align:center; margin-bottom: .5em; }
  </STYLE>  

    <xsl:for-each select="Report">
        <TABLE border="1" cellspacing="1" callpadding="1">
    <TR>
            <TD bgcolor="ff00ff">Report ID</TD>
            <TD bgcolor="ff00ff">Contact</TD>
            <TD bgcolor="ff00ff">User Fav</TD>
      </TR>            
    <TR>
            <TD width="160"><xsl:value-of select="ID" /></TD>
            <xsl:for-each select="Quota">
      
            <TD width="120"><xsl:value-of select="Contact" /></TD>
            <TD width="120"><xsl:value-of select="Fav" /></TD>
   
            </xsl:for-each>
      </TR>
  </TABLE>
   </xsl:for-each>
  </HTML>
  </xsl:template>
</xsl:stylesheet>

****************your asp file below*****
<%
  'Create XMLDOM Object
  Dim xmldoc
  Set xmldoc = Server.CreateObject("Microsoft.XMLDOM")

  If (xmldoc.childNodes.length = 0) Then
    ' Build the XML document
    Set root = xmldoc.createNode("element", "Reports", "")

    xmldoc.appendChild (root)

    ' Queries the database for customer data
    Sql = "SELECT ReportID, PersonalContact , UserFav from dailyreport"
    Set rs = conn.Execute(Sql)
 
    rs.MoveFirst

    'Loop through the recordset
    Do While Not rs.EOF
      Set onode = xmldoc.createNode("element", "Record", "")
      xmldoc.documentElement.appendChild (onode)
   
      Set inode = xmldoc.createNode("element", "ID", "")
      inode.Text = rs.fields(0)
      onode.appendChild (inode)

      Set child = xmldoc.createNode("element", "Contact", "")
        child.Text = rs.fields(1)
        inode.appendChild (child)
        Set child = xmldoc.createNode("element", "Fav", "")
        child.Text = rs.fields(2)
        inode.appendChild (child)
     
      rs.movenext
    Loop

    Set rs = Nothing
  End If

  'Save the XML doc
  xmldoc.save server.mappath("report.xml")


  '------ DISPLAY THE XML DATA ------------
  ' Linking XML and XSL together
  sourceFile = Server.MapPath("report.xml")
  styleFile = Server.MapPath("report.xsl")
 
  set source = Server.CreateObject("Microsoft.XMLDOM")
  source.async = false
  source.load(sourceFile)
  set style = Server.CreateObject("Microsoft.XMLDOM")
  style.async = false
  style.load(styleFile)
  Response.Write source.transformNode(style)
%>
***************
Dont' forget to include your database connection in the asp file above
0
 

Author Comment

by:ethar1
ID: 9628896
dear YZlat , thanks , but wot I want is creat xml document regardless database structure.
0
 
LVL 21

Assisted Solution

by:ap_sajith
ap_sajith earned 300 total points
ID: 9631363
Go through these links...

http://www.topxml.com/xml/guides/developers/ado_persist_xml.asp
http://www.4guysfromrolla.com/webtech/060601-1.shtml

OR Simply use the save method of ado...

<%
Set objRS=objConn.Execute ("Select * from <Table>")
objRS.Save Server.MapPath("test.xml"), 1
' MORE REFERENCE ON SAVE METHOD @ http://www.w3schools.com/ado/met_rs_save.asp
response.Write "Data Successfully Saved"
%>

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9753560
Hi,
Any Updates?

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9904506
Hi,
Did you manage to solve this?. Do you need further assistance with this?. Please assign the points to the suggestion(s) that helped you solve the question and close this question.

Cheers!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now