- XML Representation Options
- Building XML Response in a Server-Side Script
- Building the XML Response in the SQL Server
- Building the XML Response with XSLT
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Building XML Response in a Server-Side Script
If your SQL database doesn’t have any XML output options, you have to build the XML output you need in the server-side script.
The only failsafe way to prepare XML data in a server-side script is to do the following:
- Use Document Object Model (DOM) objects, provided by MSXML in an ASP or ASP.NET environment, or by DOM functions that are part of the PHP 5 core.
- Build the DOM tree with DOM calls.
- Write the XML representation of the DOM tree to the output stream when you’re done.
Because you usually won’t know in advance how flexible your XML output will have to be, build your server-side code as modularly as possible. Start with a subroutine that copies the values of the current SQL record into a supplied XML node. This subroutine should accept three extra parameters, in list format:
- Fields that should be encoded as attributes
- Fields that should become child tags (with the field value becoming the text of the child tag)
- Fields that hold XML or HTML fragments
Listing 5 shows the ASP version of such a subroutine.
Listing 5 Copying the current SQL record into an XML node.
Sub CopySQLToXML(RS,Node,Attributes,Children,Fragments)
Dim I,FX,SQLValue
If Not IsNull(Attributes) Then
For I = LBound(Attributes) To UBound(Attributes)
SQLValue = RS(Attributes(I)).Value
If Not IsNull(SQLValue) Then _
Node.setAttribute Attributes(I),CStr(SQLValue)
Next
End If
If Not IsNull(Children) Then
For I = LBound(Children) To UBound(Children)
SQLValue = RS(Children(I)).Value
rwbr "ch=" & Children(I) & " val=" & SQLValue
If Not IsNull(SQLValue) Then _
NewXMLTextElement Node.ownerDocument,Children(I),_
CStr(SQLValue),Node
Next
End If
If Not IsNull(Fragments) Then
For I = LBound(Fragments) To UBound(Fragments)
Rwbr "Frag=" & Attributes(I)
If Not IsNull(RS(Fragments(I)).Value) Then
Set FX = NewXMLElement(Node.ownerDocument,Fragments(I),Node)
InsertHTMLFragment FX,CStr(RS(Fragments(I)).Value)
End If
Next
End If
End Sub
On top of this basic functionality, you could build a number of additional building blocks. For example:
- A subroutine that creates a new XML child node and copies SQL data into it
- A subroutine that takes the results of an SQL query and copies all the rows into an XML tree, as shown in Listing 6
Listing 6 Copying the results of an SQL query into an XML tree.
Sub CopyRecordsetToXML(RS,Node,RowName,Attributes,Children,Fragments) Dim RX While Not RS.EOF Set RX = NewXMLElement(Node.ownerDocument,RowName,Node) CopySQLToXML RS,RX,Attributes,Children,Fragments RS.MoveNext WEnd End Sub
Armed with these functions, the server-side scripts become exceedingly simple. For example, the categories.asp script that returns the contents of the Categories table is reduced to four lines of ASP code (assuming that the DB variable contains a valid database connection), as shown in Listing 7.
Listing 7 Returning the contents of the Categories table.
Dim XC : Set XC = NewXMLObject("CategoryList")
Dim RS : Set RS = DB.Execute("SELECT * FROM Categories")
CopyRecordsetToXML RS,XC.documentElement,"Category",_
Array("CategoryID"),Array("CategoryName"),Null
OutputXMLResponse XC,Null
Similarly, only a few more lines are needed to return the contents of a frequently asked question (a row in the QA table), as shown in Listing 8.
Listing 8 Querying the SQL database and returning the selected table row.
Dim QID,QRS,QNode
QID = Request("question")
Set QRS = DB.Execute("Select * From QA Where QuestionId = " & QID)
Set QNode = NewXMLObject("QA")
If QRS.EOF Then PrintError "No such question# " & QID
CopySQLtoXML QRS,QNode.documentElement,_
Array("QuestionID","Category"),Null,_
Array("Question","Answer")
OutputXMLResponse QNode,Null