Copyright © 2008-2019 Paula DiTallo

Tag Cloud

A 'Get it Done Now!' Approach for transforming SQL Server Data to XML Data

First, for all of the MS SQL Server database purists out there that will complain vehemently against this method of extraction/formatting because of the special characters (SEE: that may (will likely) surface and need to be escaped, or that there's a better way to do this such as utilizing the XML Schema Collection/XML Explicit features available with SQL Server, etc. -- I recognize your concern, however, you'll have to admit, this is still (low-tech/mid-90's as it is) one of the fastest and most easily understood ways of simply selecting data out of existing tables and into an XML file with commonly available tools!

This method is intended for a one-time only conversion process. Anything else will require further examination of XML Explicit/XML Schema collection conversion processing.

..For that one-time's what to do....

Let's say this is the XSD schema you have for storing Sales Agent data in XML:

<?xml version="1.0" encoding="utf-16"?>
<xsd:schema id="NewDataSet" xmlns="" xmlns:xsd="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xsd:element name="SalesRep">
        <xsd:element name="First" type="xsd:string" minOccurs="0" />
        <xsd:element name="Last" type="xsd:string" minOccurs="0" />
        <xsd:element name="Email" type="xsd:string" minOccurs="0" />
        <xsd:element name="Mobile" type="xsd:string" minOccurs="0" />
        <xsd:element name="Phone" type="xsd:string" minOccurs="0" />
        <xsd:element name="Fax" type="xsd:string" minOccurs="0" />
        <xsd:element name="Address1" type="xsd:string" minOccurs="0" />
        <xsd:element name="Address2" type="xsd:string" minOccurs="0" />
        <xsd:element name="City" type="xsd:string" minOccurs="0" />
        <xsd:element name="State" type="xsd:string" minOccurs="0" />
        <xsd:element name="Country" type="xsd:string" minOccurs="0" />
        <xsd:element name="CountryName" type="xsd:string" minOccurs="0" />
        <xsd:element name="Postal" type="xsd:string" minOccurs="0" />
        <xsd:element name="Territory" type="xsd:string" minOccurs="0" />
        <xsd:element name="TerritoryState" type="xsd:string" minOccurs="0" />
  <xsd:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
      <xsd:choice minOccurs="0" maxOccurs="unbounded">
        <xsd:element ref="SalesRep" />

.... take a close look at the elements, then frame the elements for each row in a SELECT statement...

(Here's how you would write that statement)

 '<SalesRep>' +
 '<First>' + first + '</First>' +
 '<Last>' + last + '</Last>' +
 '<Email>' + email + '</Email>' +
    when Mobile is null then '<Mobile></Mobile>'
    when Mobile = '' then '<Mobile></Mobile>'
    else '<Mobile>' + PhoneCode + '-' + Mobile + '</Mobile>'
    when Phone is null then '<Phone></Phone>'
    when Phone = '' then '<Phone></Phone>'
    else '<Phone>' + PhoneCode + '-' + Phone + '</Phone>'
    when Fax is null then '<Fax></Fax>'
    when Fax = '' then '<Fax></Fax>'
    else '<Fax>' + PhoneCode + '-' + Fax + '</Fax>'
 '<Address1>' + Address1 + '</Address1>' +
    when Address2 is null then '<Address2></Address2>'
    when Address2 = '' then '<Address2></Address2>'
    else '<Address2>' + Address2 + '</Address2>'
    when City is null then '<City></City>'
    when City = '' then '<City></City>'
    else '<City>' + City + '</City>'
    when State is null then '<State></State>'
    when State = '' then '<State></State>'
    else '<State>' + State + '</State>'
 '<Country>' + Country + '</Country>' +
 '<CountryName>' + CountryName + '</CountryName>' +
 '<Postal>' + Postal + '</Postal>' +
 '<Territory>' + Territory + '</Territory>' +
    when TerritoryState is null then '<TerritoryState></TerritoryState>'
    when TerritoryState = '' then '<TerritoryState></TerritoryState>'
    else '<TerritoryState>' + TerritoryState + '</TerritoryState>'
 FROM Metro_SalesForce

This statement yields rows that will look like this:

<SalesRep><First>Jack</First><Last>Sprat</Last><Email></Email><Mobile>1-678-999-9999</Mobile><Phone>1-770-999-8888</Phone><Fax></Fax><Address1>1313 Mockingbird Lane</Address1><Address2></Address2><City>Conyers</City><State>GA</State><Country>USA</Country><CountryName>United States</CountryName><Postal>30013</Postal><Territory>Georgia</Territory><TerritoryState>GA</TerritoryState></SalesRep>

....Once you've executed the statement in SQL Server here's what's next....

1. Right click on the results grid to save the file in .csv format.
2. Open the file in Microsoft EXCEL. (Do NOT double click on the file to open Microsoft EXCEL.  Instead, open MS EXCEL first--then follow the dialog box with the format prompting to assure that you are using comma delimited, rather than fixed-length or tab formatting.)
3. In EXCEL, do a Find and replace all  -- searching for any special characters such as '&', etc.
4. Save the file.
5. Copy the file -- but use the file type .xml instead of .csv
6. Wrap the XML you have with the appropriate utf header AND node to manage the recursion of the rows (e.g. <SalesReps> . In this example that would be:

<?xml version="1.0" encoding="utf-16"?>

7. End the management node at the tail end of the file. In this case that would be:


8. Save the file-- which would look like this:

<?xml version="1.0" encoding="utf-16"?>
<SalesRep><First>Jack</First><Last>Sprat</Last><Email></Email><Mobile>1-678-999-9999</Mobile><Phone>1-770-999-8888</Phone><Fax></Fax><Address1>1313 Mockingbird Lane</Address1><Address2></Address2><City>Conyers</City><State>GA</State><Country>USA</Country><CountryName>United States</CountryName><Postal>30013</Postal><Territory>Georgia</Territory><TerritoryState>GA</TerritoryState></SalesRep>

9. Double click on the file to open it. If the file comes up in your default browser or IDE bench you're DONE! If not, check your XML header/trailer first. If it still won't come up check for other possible escape characters using EXCEL.

That's all there is to it! (Really) 
Friday, May 29, 2009 11:36 AM


No comments posted yet.

Post A Comment