Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

18 January 2008

Processing instructions and Microsoft XML DOM

If you're using Microsoft's XML DOM you'll find it strips out the encoding attribute from your processing instruction (the <?xml ... ?> bit) when you get the value of the Xml property. This is because it always returns unicode regardless of what the input encoding was. As unicode is the default for the XML specification, no encoding attribute is required.

The Save method, which writes the contents of the DOM document to a file, maintains the original encoding. If you need your XML in the original encoding after some manipulation in the DOM then you can do this:

xmldoc.Save("c:\blah.xml")
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile("c:\blah.xml")
strXml = ts.ReadAll

Programmatically changing a processing instruction

Looks a bit dodgy but this is the way to do it:

Set pi = xmldoc.createProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-16""")
xmldoc.replaceChild pi, xmldoc.childNodes.Item(0)

13 February 2007

XML namespace prefixes in MSXML

If you're working with XSL or a similar technology that makes use of XML namespace prefixes using the Microsoft XML DOM you'll likely run into problems if you try to do anything more than just load in a file.

Adding elements

The W3 DOM specification includes a createElementNS method for creating an element scoped within a namespace however MSXML doesn't. You can create an element with a prefix using createElement but this doesn't correctly register the namespace of the node and you'll get a schema error something like:

msxml3.dll: Keyword xsl:stylesheet may not contain xsl:include.

In order to create an element and register it correctly you have to use createNode instead which takes node type (1 for an element), node name and namespace URI as arguments e.g.

Set ndIncl = xslDoc.createNode(1, "xsl:include",
"http://www.w3.org/1999/XSL/Transform")

Using XPath

Similar to the createElement problem, even if you've only loaded an XSL document you won't be able to use XPath to query it because oddly the namespaces aren't automatically registered with XPath e.g.

Set nlTemps = xslDoc.documentElement.selectNodes("/xsl:stylesheet/xsl:template")

yields the following error:

msxml3.dll: Reference to undeclared namespace prefix: 'xsl'.

To get this to play ball you have to set the "SelectionNamespaces" second-level property which takes a space delimited list of namespace definitions using a setProperty call of the form:

xslDoc.setProperty "SelectionNamespaces",
"xmlns:xsl='http://www.w3.org/1999/XSL/Transform'"

Links

05 February 2007

XML and SQL Server

In this post i'll cover how you can get SQL Server to return data as XML by using the FOR XML command and how you can use XML as input for updating records and as a rich argument for row returning stored procedures using the OPENXML command.

There are lots of reasons you may want to get data out of a database as XML:

  • You may be building an AJAX app and want to send XML to the client directly for processing by your client-side JavaScript
  • You may want to use XSL to transform your data into some format such as HTML or a CSV
  • You may want to export data and store it in a form which retains its original structure

These reasons also give you reasons for needing to pass XML into your database for example with the AJAX app you may want to receive changes as XML from the client and post them straight to a stored proc that updates your tables.

Examples are in VBScript using ASP and ADO.

Getting SQL Server to return XML

The key to getting SQL Server to return XML is the FOR XML command. It comes in three flavours:

FOR XML RAW
The least useful, RAW mode simply outputs the rows returned by your query as <row> nodes with the columns being either elements within this node or attributes of it as you define.
FOR XML AUTO
Automagically translates your SQL query, joins and all into suitable nested XML elements and attributes. For example if you are joining Orders to OrderItems the XML output will be OrderItem nodes nested within the associated Order node. You can alter the naming of the nodes by aliasing your table and column names but that's about it.
FOR XML EXPLICIT
Explicit mode allows the most customisability but it's also the most fiddly requiring you to alias all your columns names to a specific format which describes which nodes they should belong to.

You'll mostly use AUTO mode because it gives you the most useful results in the least amount of time so here it is in an example:

SELECT Order.*, OrderItem.*
FROM Order
INNER JOIN OrderItem
   ON Order.order_key = OrderItem.order_fkey
WHERE Order.customer_fkey = 1
FOR XML AUTO

All you do is tag FOR XML AUTO on to the end of your query, that's it! The output will look something like this:

<Order order_key="1" customer_fkey="48" date_placed="24/08/2006 12:31">
   <OrderItem orderitem_key="123" order_fkey="1" product_fkey="234" list_price="£14" />
   <OrderItem orderitem_key="124" order_fkey="1" product_fkey="64" list_price="£3" />
   <OrderItem orderitem_key="125" order_fkey="1" product_fkey="73" list_price="£27" />
</Order>

If you run this in Query Analyzer you'll notice in the results pane it looks like the XML has been split into rows. We need to use an ADODB.Stream object to get at the output properly, thus:

Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
Set strm = Server.CreateObject("ADODB.Stream")

conn.Open "Provider=SQLOLEDB;Data Source=myServerAddress;" & _
   "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

strm.Open

Set cmd.ActiveConnection = conn

cmd.Properties("Output Stream").Value = strm
cmd.Properties("Output Encoding") = "UTF-8"
cmd.Properties("XML Root") = "Root"  'this can be anything you want

cmd.CommandType = adCmdText
cmd.CommandText = strSQL

cmd.Execute , , adExecuteStream

Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"

xmlDoc.LoadXML(strm.ReadText)

strm.Close : Set strm = Nothing
Set cmd = Nothing

xmlDoc now contains our XML to do with as we will.

Passing XML into SQL Server

The easiest way to get XML into SQL Server is as a parameter of a stored procedure thus:

cmd.Parameters.Append cmd.CreateParameter("somexml", adVarChar,
adParamInput, 8000, xmlDoc.xml)

You then use two System Stored Procedures along with the OPENXML command to SELECT from the contents of the XML parameter as if it were a table:

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @somexml

SELECT * FROM OPENXML (@idoc, '/Root/Order') WITH (Order)

EXEC sp_xml_removedocument @idoc

OPENXML takes the prepared XML document and an XPath expression telling it which nodes it is taking into account. The WITH statement in this case tells OPENXML that the nodes it is working on are of the same schema type as the rows in the Order table.

The result of this call is a list of records of the same schema as the Order table but which have actually come from the passed in XML document. Because the schema is that of Order you can put an INSERT INTO [Order] in front of the SELECT and this will add the rows from the XML to the Order table. You probably wouldn't want to do that but you get the idea.

You don't have to have a table representing the schema of the XML you're passing in in your database. WITH also accepts a normal schema declaration i.e. comma delimited column names with their types and which node this maps to in the XML:

SELECT order_key, customer_fkey, description
FROM OPENXML (@idoc, '/Root/Order') 
WITH (
   order_key     int           '@order_key',
   customer_fkey int           '@customer_fkey',
   description   nvarchar(100) 'description'
)

The advantage of being able to do this is that you can pass complex structured criteria into one of your stored procedures and use OPENXML to turn it into a rowset which you can use to JOIN to the tables in your database. Powerful stuff with a large number of applications in both improving querying data and updating it.

15 December 2006

Working with XML - Part 3 - Formatting XML using XSL

XSL is an language for transforming XML into different formats, written in XML using specific elements and attributes. To achieve the transform you simply load you XML and XSL into DOM objects then call transformNode on the XML document passing it your XSL document and it will return the transformed output.

XSL is effectively a procedural programming language. It has conditional and looping logic structures as well as variables and callable "functions". I'd recommend reading XSL @ W3SChools as this explains the basics well.

Recap

In parts one and two we looked at loading a sample bit of XML and how to select certain nodes from it using XPath.

<?xml version="1.0" ?>
<library>

  <authors>
     <author id="12345">
        <name>Charles Dickens</name>
     </author>
     <author id="23456">  
        <name>Rudyard Kipling</name>

     </author>        
  </authors>
  <books>
     <book>
        <title>Great Expectations</title>
        <author>12345</author>

     </book>
     <book>
        <title>The Jungle Book</title>
        <author>23456</author>
     </book>

  </books>
</library>

Using XSL we can convert this XML document into, for example, HTML to send to a web browser. We could also transform it into SQL INSERT statements for adding the data to a database.

HTML Output - Listing Data

Here's an example of how you could output the records from the sample XML:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<!-- more stuff to go here -->

</xsl:stylesheet>

We start with the stylesheet element which specifies the XML namespace "xsl" which is used as the prefix for all the special XSL elements.

<xsl:template match="/">
   <html>
      <head>
         <title>Library</title>
      </head>
      <body>
         <h1>Library</h1>
         <xsl:apply-templates select="authors/author" />
      </body>
   </html>
</xsl:template>

Next we have a template element with a match attribute equal to "/". This template matches the root of the XML document so the transform starts here outputting the contents of the element. The apply-templates element then tells the transform to apply the appropriate templates to the nodes returned by the value of the select attribute which you'll notice is XPath.

<xsl:template match="author">
   <h2><xsl:value-of select="name" /> (<xsl:value-of select="@id" />)</h2>
   <p>Books by this author:</p>
   <table>
      <tr>
        <th>Title</th>
      </tr>
      <xsl:apply-templates select="/library/books/book[author = current()/@id]" />
   </table>
</xsl:template>

This template will match the author nodes selected so output will pass here at this point - similar to a function call in a normal programming language. Here value-of elements output the value of the nodes identified in their select attributes, XPath again.

The next apply-templates element selects all the book elements with an author child element whose value is equal to the current author element's id attribute. It uses the current() XSL function to get at the element being tranformed by the template.

<xsl:template match="book">
   <tr>
      <td><xsl:value-of select="title" /></td>
   </tr>
</xsl:template>

Finally the titles of the books are writen out on table rows so what you end up with after calling transformNode is this:

<html>
  <head>
    <title>Library</title>
  </head>
  <body>
    <h1>Library</h2>
    <h2>Charles Dickens (12345)</h2>
    <p>Books by this author:</p>
    <table>
      <tr>
        <th>Title</th>
      </tr>
      <tr>
        <td>Great Expectations</td>
      </tr>
    </table>
    <h2>Rudyard Kipling (23456)</h2>
    <p>Books by this author:</p>
    <table>
      <tr>
        <th>Title</th>
      </tr>
      <tr>
        <td>The Jungle Book</td>
      </tr>
    </table>
  </body>
</html>

Using the output element

XHTML compliant output

XSL has an HTML output mode that you can specify by adding this to the top of your stylesheet, before any template elements:

<xsl:output mode="html" />

However the Microsoft.XMLDOM will mess around with your tags if you use this so if you want your output to be XHTML compliant you need to use the XML output mode instead thusly:

<xsl:output method="xml" omit-xml-declaration="yes" />

Outputting a DOCTYPE

If you want to output a DOCTYPE (which you need to get IE to obey the CSS box model properly) you add a few attributes to your output element:

<xsl:output 
   method="xml" 
   omit-xml-declaration="yes"
   doctype-public="-//W3C//DTD XHTML 1.0 Strict//EN"
   doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
/>

which will produce:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

For more info on the output element visit output @ W3Schools.

Useful XSL snipets

Alternating row classes on tables

<xsl:template match="book">
   <tr>
      <xsl:attribute name="class">
         <xsl:choose>
            <xsl:when test="(position() mod 2) = 0">even</xsl:when>
            <xsl:otherwise>odd</xsl:otherwise>
         </xsl:choose>
      </xsl:attribute>
      <td><xsl:value-of select="title" /></td>
   </tr>
</xsl:template>

Template applicability

You can make nodes of the same type product different output by changing the match attribute of your template:

<xsl:template match="book[author = 12345]">
   <tr>
      <td class="highlight"><xsl:value-of select="title" /></td>
   </tr>
</xsl:template>

<xsl:template match="book">
   <tr>
      <td><xsl:value-of select="title" /></td>
   </tr>
</xsl:template>

Here we're applying a highlight class to book rows by author 12345.

Links

06 November 2006

Working with XML - Part 2 - Using XPath to query XML

XPath is a simple language for querying XML documents in order to retrieve nodes matching particular criteria. There are some good references and tutorials out there to help you get to grips with the basics; i'd recommend reading XPath @ W3Schools for starters and then running through the Zvon XPath Tutorial before reading on.

XSL, which the next part of this post is about, makes much use of XPath so you need to get up to speed with it before you venture into XSL.

Recap

In part one we loaded the following XML in to the DOM and performed various operations with DOM properties and methods. To use XPath there are only two methods selectNodes which returns a node list and selectSingleNode which returns one node.

<?xml version="1.0" ?>
<library>
  <authors>
     <author id="12345">
        <name>Charles Dickens</name>
     </author>
     <author id="23456">  
        <name>Rudyard Kipling</name>
     </author>        
  </authors>
  <books>
     <book>
        <title>Great Expectations</title>
        <author>12345</author>
     </book>
     <book>
        <title>The Jungle Book</title>
        <author>23456</author>
     </book>
  </books>
</library>

XPath allows you to do some quite complex data selection and analysis using a mixture of path syntax, axes, predicates and functions. Having said that i had quite a hard time finding decent examples of doing some quite simple stuff.

Important - Setting the SelectionLanguage property

If you're using the Microsoft XMLDOM COM component you need to set the SelectionLanguage property of the DOM document to "XPath" otherwise you'll get some very odd results - you do this as follows:

xmlDoc.setProperty "SelectionLanguage", "XPath"

Example 1 - Selecting nodes and checking return value

'a single node
strXPath = "/library/authors"
Set ndAuthors = xmlDoc.documentElement.selectSingleNode(strXPath)

'This test checks whether authors was found or not...
If ndAuthors Is Nothing Then
  'error not found!
Else
  'do something with authors
End If

'multiple nodes
strXPath = "/library/authors/author"
Set nlAuthors = xmlDoc.documentElement.selectNodes(strXPath)

'This test checks whether author nodes were found or not...
If nlAuthors.Length = 0 Then
  'error not found!
Else
  For Each ndAuthor In nlAuthors
     'do something with nodes
  Next
End If

If you ran through the Zvon XPath tutorial earlier you should now be able to do some basic selecting of nodes using the two methods i've just shown you.

In the next few examples i'm going to run through some of the things you'll probably want to do but tutorials like the Zvon one don't cover.

Example 2 - Predicates and Axes

Selecting the author element with id "12345"...

strXPath = "/library/authors/author[@id='12345']"
Set ndNode = xmlDoc.documentElement.selectSingleNode(strXPath)

Only selecting the author's name...

strXPath = "/library/authors/author[@id='12345']/name"
Set ndNode = xmlDoc.documentElement.selectSingleNode(strXPath)

Titles of books written by that author...

strXPath = "/library/books/book[author='12345']/title"
Set nlNodes = xmlDoc.documentElement.selectNodes(strXPath)

Example 3 - Functions

Simple counting of nodes...

strXPath = "count(/library/authors/author)"
Set ndCount = xmlDoc.documentElement.selectSingleNode(strXPath)

Combining count with the ancestor axis allows you to select nodes of a particular depth...

strXPath = "//*[count(ancestor::*) > 2]"
Set nlDeepNodes = xmlDoc.documentElement.selectNodes(strXPath)

Books with "The" in their title...

strXPath = "/library/books/book[contains(title,'The')]"
Set nlNodes = xmlDoc.documentElement.selectNodes(strXPath)

Example 4 - Common tasks

Remove nodes that match certain criteria...

strXPath = "/library/books/book[contains(title,'The')]"
Set nlNodes = xmlDoc.documentElement.selectNodes(strXPath)
For Each ndNode In nlNodes
   ndNode.parentNode.removeChild ndNode
Next

Links

21 October 2006

Working with XML - Part 1 - Using the DOM

XML (Extensible Markup Language) is a way of storing information as text by applying structure and meaning to data using a system of nested elements and attributes. HTML is a loose form of XML because it consists of elements, attributes and text although it doesn't always obey the strict rules necesary for valid XML.

The basic rules are:

  • Element and attribute names are case sensitive i.e. derek != Derek
  • Each element must be closed
  • All an element's child elements must be closed before it can be
  • An XML document must have one element only as its root node

Document Type Definitions (DTD) and XML Schema Definition (XSD) are methods for defining the structure of an XML document and ensuring it adheres to your specification. Although i'm not going to cover them in this post they're very important particularly if you're letting other people write XML for your system.

Examples are in ASP/VBScript

Example 1 - Some XML

<?xml version="1.0" ?>
<library>
   <authors>
      <author id="12345">
         <name>Charles Dickens</name>
      </author>
      <author id="23456">   
         <name>Rudyard Kipling</name>
      </author>         
   </authors>
   <books>
      <book>
         <title>Great Expectations</title>
         <author>12345</author>
      </book>
      <book>
         <title>The Jungle Book</title>
         <author>23456</author>
      </book>
   </books>
</library>

In this example we have a library element as the root node of the XML document. Inside that we have an authors element containing author elements and a books element containing book elements. The author elements have an id attribute and a name child element whereas the book elements have a title element and an author element containing the id of the associated author.

You can see from this example how it's easy to denote quite complex relationships in a simple, readable manner. I'll base the other examples in this post around this bit of XML

Example 2 - Loading XML in to a DOM

Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async = False
xmlDoc.load(Server.MapPath("mydoc.xml"))
Response.Write xmlDoc.xml

This snipet loads and XML document from a file into a DOM object. There's also a LoadXML function on the Microsoft DOM object for loading a string containing XML.

Once we've got our XML loaded we can traverse the tree, read data, change properties and save it back to a file.

Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async = False
xmlDoc.load(Server.MapPath("mydoc.xml"))
Set ndRoot = xmlDoc.documentElement

'retrieving element names
Response.Write ndRoot.tagName & "<br />"

'looping though nodes
Set ndAuthors = ndRoot.firstChild
For Each ndAuthor In ndAuthors.childNodes
   Response.Write ndAuthor.getAttribute("id") & "<br />"
Next

'setting attributes
Set ndSecondAuthor = ndAuthors.childNodes(1)
ndSecondAuthor.setAttribute "id", 99999
Response.Write ndSecondAuthor.getAttribute("id") & "<br />"

'retrieving and setting node text
Response.Write ndSecondAuthor.firstChild.text & "<br />"
ndSecondAuthor.firstChild.text = "Joe Bloggs"
Response.Write ndSecondAuthor.firstChild.text & "<br />"

Links