XML Tips from perfectxml.com [Page 1]
Using FOR XML, MSXML and ADO to export SQL Server 2000 table as XML
There are many ways to get SQL Server 2000 data in XML format. Let's look at one of these ways, which uses ADO 2.6, MSXML 3.0 and SQL Server 2000 FOR XML T-SQL clause. Remember to add reference to ADO 2.6 and MSXML 3.0. Note that no error handling or cleanup is done in the following code for the sake of brevity.
Dim ADOConn As New ADODB.Connection
Dim ADOComm As New ADODB.Command
Dim ADOStream As New ADODB.Stream
Dim MSXMLDoc As New MSXML2.DOMDocument30
Dim XMLString As String
ADOComm.ActiveConnection = ADOConn
ADOComm.CommandType = adCmdText
ADOComm.CommandText = "SELECT * FROM Customers FOR XML RAW"
ADOComm.Properties("Output Stream").Value = ADOStream
ADOComm.Execute , , adExecuteStream
XMLString = "<?xml version='1.0' ?>" & vbNewLine & "<Customers>"
XMLString = XMLString & vbNewLine & ADOStream.ReadText
XMLString = XMLString & "</Customers>"
- You can very easily import XML document data into Microsoft Access 2002 (XP). Simply make sure that your XML document has following structure:
And then, start Microsoft Access, create a blank Access 2002 database (or open an existing one) and choose File | Get External Data | Import, and select your input XML file.
Related KB Articles:
- The FreeThreadedDOMDocument has no thread affinity, hence it may be used in applications where different threads need to access the object (for example, in an ASP application, where you want to store XML in a Session or Application variable). In contrast, if you're using the object from a stand-alone application or only on the client, use a DOMDocument. To use the free-threaded control provided with MSXML 4.0, use the "Microsoft.FreeThreadedDOMDocument.4.0" ProgID. Also, note that programmatically passing a parameter to a stylesheet requires the free threaded model.In summary, If you plan for several threads to access your XML data from a single control, be sure to use the free-threaded control. If only one thread will access the XML data, use the rental model control for better performance.
- The default XML parser for Internet Explorer 5.5 is MSXML 2.0. You can use xmslinst.exe utility to change the default parser to MSXML 2.6 or MSXML 3.0 - but it is important to remember that this can often cause unintended side-effects for some applications. This is the reason why such a practice is not enabled for MSXML versions 4.0 and later. Hence, until Internet Explorer ships with MSXML 4.0 as its default XML parser, the MSXML 4.0 features are available in Internet Explorer only via scripting when an XML DOM object is instantiated using the MSXML4.0-specific ProgID.
- Remember that MSXML (Microsoft XML Parser) 3.0 SP 2 by default installs in replace mode. If you are using old XSL namespace (http://www.w3.org/TR/WD-xsl), but want to move to the W3C recommendation XSLT namespace (http://www.w3.org/1999/XSL/Transform) for client side XSL Transformation, and cannot assume that the clients will have Internet Explorer 6.0, you can have them install MSXML 3.0 SP2 and your XSLT transformations should then work with the new (and correct) XSLT namespace. MSXML 3.0 SP 2
- XML Validator and Transformations
Try our tiny but useful utility to validate XML and apply transformations
While you type your XML document, this page will validate the text and report exact errors, if any. You can also write an XSLT transformation and view the transformed results immediately on the same page! Check out this small utility page!
- ADO & XML
You can save ADO Recordset in XML format in file or as an instance of MSXML DOM object. You can then load ADO recordset from that XML file.
ex 1: Saving Recordset into .xml file.
oRecSet.Save "C:\Rec.xml", adPersistXML
ex 2: Saving RecSet as MSXMLDOM object
Dim xDOM As New MSXML.DOMDocument
oRecSet.Save xDOM, adPersistXML
- Loading a XML document
While loading a XML document using DOM, by default the file is loaded asynchronously. Here it is important to examine document's ReadyState property to insure a document is ready. By setting document's async property to false, the parser will not return the control to your code until the document is completely loaded and ready for manipulation.
Dim xDoc as New MSXML.DOMDocument
xDoc.async = False
If xDoc.Load("https://perfectxml.com/test.xml") Then
'The document loaded successfully.
'The document failed to load.
ADO+ is the functional superset of ADO 2.6 (except server cursors.) It has great support for XML - knows how to load and save XML.
ADO+ also supports DOM, DTD, XSL/T, and X-Path.
SAX2, the Simple API for XML, is the latest version of standard interface for event-based XML parsing. SAX2 offers a fast, low-memory alternative to processing XML documents using the DOM. When the DOM is used to parse an XML file, it builds a complete tree of document in memory. SAX2, in contrast, traverses the document and informs the calling application of parsing events, such as start/end of a new element.
"XML is only for web"
"HTML is a subset of XML"
"XML stands for Excellent Marketing Language"
"XML = 1040 (in roman)"
"S", "M", "L", "XL", "XML"
Tag Rules for XML Documents
XML tags can't overlap. <a><b></a></b> isn't allowed. If you start a <b> tag inside an <a> tag, you have to end it inside the <a> tag as well.
You can't leave out any end tags. Tags like </p> and </br> are required.
Tags that don't contain any text can contain the end marker at the end of the start tag. In other words, <br></br> is equivalent to <br />.
- XSL ISAPI Filter 2.0
The Microsoft XSL ISAPI Filter enables server-side XSL formatting for multiple device-types.
It features automatic execution of XSL style sheets on the server, choosing alternate style sheets based on browser type, style-sheet caching for improved server performance, the capability to specify output encodings, and customizable error messages. Read more...