Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need to Convert Relational Data (SQL Server)  to XML using XSD

Posted on 2009-05-20
8
Medium Priority
?
558 Views
Last Modified: 2012-05-07
SQL/XML Brainiacs and Wizards--

I am trying to convert existing non-XML formatted string data from a view.  I generated an XSD file for the XML format I need to output.  The view's columns/elements are an exact match to the xsd (see select view  script below)  -- so First is First, Last is Last, Email is Email, etc.  I was able to create the schema collection (see code snippet) --however I do not know how to route a select statement from the view to use this --

Please advise...

---------   select view script ------------

SELECT [First]
      ,[Last]
      ,[Email]
      ,[Mobile]
      ,[Phone]
      ,[Fax]
      ,[Address1]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Country]
      ,[CountryName]
      ,[Postal]
      ,[Territory]
      ,[TerritoryState]
  FROM [MyMetroServer].[dbo].[VW_SalesForceInterNtl]
CREATE XML SCHEMA COLLECTION SalesRep
  AS N'<?xml version="1.0" encoding="utf-16"?>
<xsd:schema id="NewDataSet" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xsd:element name="SalesRep">
    <xsd:complexType>
      <xsd:sequence>
        <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:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xsd:complexType>
      <xsd:choice minOccurs="0" maxOccurs="unbounded">
        <xsd:element ref="SalesRep" />
      </xsd:choice>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
 
declare @x XML(CONTENT SalesRep);

Open in new window

0
Comment
Question by:Paula DiTallo
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
Hwkranger earned 900 total points
ID: 24436073
See:  http://msdn.microsoft.com/en-us/library/ms345117.aspx

and

http://sqlxml.org/faqs.aspx?faq=102

Look at this, will return XML but not in your schema.  Look at the FOR XML EXPLICIT to get your exact schema.

SELECT [First]
      ,[Last]
      ,[Email]
      ,[Mobile]
      ,[Phone]
      ,[Fax]
      ,[Address1]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Country]
      ,[CountryName]
      ,[Postal]
      ,[Territory]
      ,[TerritoryState]
  FROM [MyMetroServer].[dbo].[VW_SalesForceInterNtl]

Open in new window

0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24436086
Correction:
SELECT [First]
      ,[Last]
      ,[Email]
      ,[Mobile]
      ,[Phone]
      ,[Fax]
      ,[Address1]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Country]
      ,[CountryName]
      ,[Postal]
      ,[Territory]
      ,[TerritoryState]
  FROM [MyMetroServer].[dbo].[VW_SalesForceInterNtl]
FOR XML AUTO

Open in new window

0
 

Author Comment

by:Paula DiTallo
ID: 24436497
Hi Techies--
While waiting on some feedback, I almost solved the issue by writing attached SQL--but-- the line is truncated. Anyone know how to extend the line size?

Here's an example of the output:

<SalesRep><First>Angelica</First><Last>Souto</Last><Email>angelica.souto@shawinc.com</Email><Mobile>1- 305 796 1999</Mobile><Phone>1-305 827 5950</Phone><Fax>1-305 827 5910</Fax><Address1>5881 Northwest 151 Street</Address1><Address2>Suite 100A</Address2><




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

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 24437676
That would be because you have the output in SQL Query Analyzer (or whatever tool you are using) set to a max of 256 bytes.  Try increasing it.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 24437681
By the way, I should point out to you that your approach is risky to say the least (and I won;t even talk about performance), here is one reason:  If you have any special characters they will need to be escaped.
0
 

Author Comment

by:Paula DiTallo
ID: 24443466
As the old adage goes...any port in a storm will do... especially when pressed for time!;-) the task at hand is completed; the escape characters found and managed. Since this was a one-time change over, I wasn't worried about performance. To deal with the rows/escape chars/buffer issue I just output the cvs data from the grid to excel -- did a search and replace on the escape characters.... the process worked fine--even though this method for conversion is far from the preferred way.

Now that the task is complete and I have a little more time to exam the best practices approach, let's reexamine my first approach for a moment. After posting, Hwkranger suggested that I use XML Explicit rather than the Schema Collection I first had in mind to do. I'll look at XML Explicit today--but I am interested in knowing why I can't select from a table/view using the XSD schema collection--I would think it would be more efficient than XML Explicit even--since its all spelled out. Any thoughts?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24446727
>>Any thoughts?<<
I am afraid the "B" grade deflated any enthusiasm I may have had with this question.  Try posting a new question and hopefully someone will step up to the plate.

Good luck.
0
 

Author Comment

by:Paula DiTallo
ID: 24454987
Fair enough!;-) Thanks for taking the time you did to answer the part of the question you could.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question