Using Data Transformation Services: Can We Transfer XML Data?
(Page 1 of 5 )
There has been a lot of discussion in the SQL forums on whether or not DTS has XML support. This investigative tutorial looks at this issue with some interesting observations.
Introduction
One of the chief problems with DTS having XML support is that the SQL2000 support for XML, in the form of language enhancements to the Transact SQL for two way traffic between XML and relational data works well; however, it is not adequate for handling the traffic in the DTS context. The main reason is that the XML that is returned by the forXml clause comes out with characters that are not parsed by the XML parser. We will see some aspects of this in the tutorial.
It is possible to recover the XML that is processed by DTS, but may require some post processing. An elegant, purely ActiveX script based answer gleaned from an Internet link is also described. To understand this article better, please review the following tutorials, on forXML and DTS Global variables.
The forXML clause
In going from relational data to XML, SQL 2000 Server has enhanced the Select statement. SQL 2000 server returns a single column of XML formatted data that may exceed the size of a query return in the query analyzer. In returning the single column of formatted data in XML, the server runs the query and then XML formats the results depending on the particular clause, as in the following extensions:
Select <> From <>
[where]<>
[order by]<>
[for xml (raw|auto[,elements]|
explicit)[,xmldata]
[,binary base64])]
For XML Raw returns rows in a generic row identifier <row>. The intersections for that row with the columns are returned as attributes in name/value pairs. This data may need further processing to map attributes to elements. For XML raw can have further qualifiers, binary base64 and XMLData. The for xml raw, xmldata returns a schema as well.
The next query is run against the pubs database in SQL 2000 server. This may be copied into the Query Analyzer and executed. The following picture shows the results returned (it starts with the schema first and then the rowset). As the result is stuffed into a column, in the query analyzer's result pane it may look garbled, but it can be copied and pasted into notepad. The results also lack a <root>element, and therefore they do not give you a well formed XML document, but an XML fragment. In order to view the result in a browser, the returned result has to be enclosed inside <root></root >. The result, as seen in the browser after this, is shown in the next picture (the latter part is truncated to save space).
select pub_id, price, type,ytd_sales,titleauthor.title_id,au_ord
from titles,titleauthor
where titleauthor.title_id=titles.title_id
and price>15
order by pub_id
for xml raw, xmldata
However if you capture the complete result from the query analyzer for the above query on Northwind you would see the following. This is a malformed XML document, even if you were to get rid of the first line. In the picture above I removed this line and then enclosed the result in betweentags.
XML_F52E2B61-18A1-11d1-B105-00805F49916B <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="row" content="empty" model="closed"><AttributeType name="pub_id" dt:type="string"/><AttributeType name="price" dt:type="fixed.14.4"/><AttributeType name="type" dt:type="string"/><AttributeType name="ytd_sales" dt:type="i4"/><AttributeType name="title_id" dt:type="string"/><AttributeType name="au_ord" dt:type="ui1"/><attribute type="pub_id"/><attribute type="price"/><attribute type="type"/><attribute type="ytd_sales"/><attribute type="title_id"/><attribute type="au_ord"/></ElementType></Schema><row xmlns="x-schema:#Schema1" pub_id="0736" price="19.9900" type="psychology " ytd_sales="4072" title_id="PS3333" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="0877" price="19.9900" type="mod_cook " ytd_sales="2032" title_id="MC2222" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="0877" price="21.5900" type="psychology " ytd_sales="375" title_id="PS1372" au_ord="2"/><row xmlns="x-schema:#Schema1" pub_id="0877" price="21.5900" type="psychology " ytd_sales="375" title_id="PS1372" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="0877" price="20.9500" type="trad_cook " ytd_sales="375" title_id="TC3218" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="20.0000" type="popular_comp" ytd_sales="4095" title_id="PC8888" au_ord="2"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="25.0000" type="popular_comp" title_id="PC9999" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="19.9900" type="business " ytd_sales="4095" title_id="BU1032" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="20.0000" type="popular_comp" ytd_sales="4095" title_id="PC8888" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="19.9900" type="business " ytd_sales="4095" title_id="BU1032" au_ord="2"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="22.9500" type="popular_comp" ytd_sales="8780" title_id="PC1035" au_ord="1"/><row xmlns="x-schema:#Schema1" pub_id="1389" price="19.9900" type="business " ytd_sales="4095" title_id="BU7832" au_ord="1"/> (12 row(s) affected)
Next: Implementing DTS Transfer of XML >>
More Database Articles
More By Jayaram Krishnaswamy
/ 10 
