Solved

Write XML file using VBScript

Posted on 2003-10-27
11
4,143 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

839 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