Contents of xmls2ke3.asp:
<html> <head> <SCRIPT language="javascript"> var c=1; xml_string="<p>"; function ol() { var root=data_island.documentElement; if (data_island.parseError !=0) alert("XML PARSE ERROR="+data_island.parseError.reason); treewalk(root) xout.innerHTML = xml_string+"<p>Number of rows="+parseInt(c); } function treewalk(thisnode) { xml_string=xml_string+"<p>"+thisnode.childNodes.length; for(i=0;i<thisnode.childNodes.length;i++) { xml_string=xml_string+"<br>i="+i+"<br>"; xml_string=xml_string+"<br><"+thisnode.childNodes(i).nodeName; for(j=0;j<thisnode.childNodes.item(i).attributes.length;j++) { xml_string=xml_string+" "+thisnode.childNodes(i).attributes.item(j).nodeName; xml_string=xml_string+"="+String.fromCharCode(34); xml_string=xml_string+thisnode.childNodes(i).attributes.item(j).text+String.fromCharCode(34); } xml_string=+"/>"; c=c+1; } xout.innerHTML=xml_string; } </SCRIPT> </head> <body bgcolor="#cccccc" onLoad="ol()"> <table border="1"> <tr><td valign='middle' bgcolor='#999999'><img src="captsm.gif"></td> <td valign='middle' bgcolor='#999999'><center><font color="#ffffff"><font face="Comic Sans MS"> <br> <b>A SIMPLE XML EXTRACT USING SQL SERVER 2000<br> "SELECT...FOR XML AUTO"</b> <br> </td></tr></table> </center> <p> <font face="Comic Sans MS" size="2"> <p> The SQL connection string is ="SELECT * from glmaster <b>FOR XML AUTO</b>" <p> Note: The connection is OLEDB <u>NOT</u> DSN. <p> MS SQL Server 2000 returns 1 nested tag <u>per</u> table row where: <ol> <li>the outside tag is the table name <li>each table row's "SELECTED" columns appears as a tag pair with the column data as content <li>there is <u>NO</u> overall outside tag<br> (Note: You have to add one to use the XML DOM on the client side) </ol> <p> The XML is returned to the client as an XML data island and rolled out through javascript using the XML DOM.<br> NOTE: The roll out is not recursive since the "XML AUTO" will only return two levels. <p> Click "VIEW", then "SOURCE" to see the XML data island and the javascript XMLDOM rollout Execution begins... <p> <form name="xmltest"> <XML id="data_island"><?xml version="1.0"?><outside_tag> <% set cn=Server.CreateObject("ADODB.Connection") cn.open "Provider=SQLOLEDB; Data Source=AUCKLAND; Initial Catalog=gl001; User ID =gl001; Password=11111;" set cm=Server.CreateObject("ADODB.Command") set cs=CreateObject("ADODB.Stream") set cm.ActiveConnection = cn cm.CommandType = 1 '*** Text cm.CommandText="SELECT * from glmaster FOR XML AUTO" cs.mode=1 cs.Type=2 cs.open cm.Properties("Output Stream").value = cs cm.execute,,1024 Response.Write cs.ReadText cn.close set cn=nothing set cm=nothing set cs=nothing %> </outside_tag> </XML> <div id="xout"></DIV> <p> Click <a href="bdoc.asp?fl=x4"> here</a> for the source code </body> </html>
***** end of file *** printed: 101 lines on: 11/23/2009 6:12:40 AM
