May 2009 Entries

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) 
Posted On Friday, May 29, 2009 11:36 AM | Comments (0)
Filed Under [ SQL ]

This is a simple, but helpful example of changing a boolean or bit flag in a table to 'True' when the condition in the subSelect is met.  In this context, the SQL statement finds all the cases where a sales agent's email exists in the Metro_SalesForce table and exists in the related table Metro_SalesForceMult. The Metro_SalesForceMult table contains the USA state assignments only for sales agents representing more than a single state.

UPDATE Metro_SalesForce
 SET multipleStates = 'True'
       From Metro_SalesForce x, Metro_SalesForceMult y
        Where =
Posted On Friday, May 29, 2009 10:34 AM | Comments (1)
Filed Under [ SQL ]
usually your host file (DNS/ip addresses) can be found under:

This can be a really irritating problem when suddenly the EXCEL file on your desktop you used to double-click on no longer gets viewed via Microsoft EXCEL, but instead gets viewed through the Microsoft Excel READER program instead!   In essence, either a new program you've installed has reset the file association, or you are working on a new computer that may have different software--overall, this area has to do with File Management/File Extension types.

Here's what to do:

In Windows XP:
1. Look for the 'My Computer'  icon on your desktop (usually to the far left, near the top of your monitor)
2. Double-click on the icon. A window will appear. Go to 'Tools'. Click on Folder Options...
3. Look to make sure the  'Hide Extensions for known file types' box is unchecked. Click 'Ok',
4. Go to the File Types tab.
5. Look for the File extension (e.g. XLS) you want to change.
6. Once found, highlight it and click on the Change button.
7. Under recommended programs, the one you want is probably there. Be sure the 'Always use the selected program to open this kind of file' is checked. Click OK.  (Note: If you can't find your program under recommended programs, continue to scroll until you find the one you want.)

In Vista:
1. open Control Panel. Go to Programs. Click on Make a file type always open in a specific program. ( if you are in classic view, open Default Programs. Click Associate a file type with a program.
2. Locate/highlight a file type in the Set Associations folder.
3. Click on the Change Program button.
4. The Set Associations window will show--pick your program--using the same advice in Step #7 above.
5. When you've selected the program, Click OK.

That should patch you right up!   

With that all that said:

If you do not find the program you are looking for, do not try to change the existing association for these files. First determine why the program you think is there no longer appears to be there.