Solved

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

Posted on 2009-05-20
8
528 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:ditallop
  • 3
  • 3
  • 2
8 Comments
 
LVL 9

Accepted Solution

by:
Hwkranger earned 300 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:ditallop
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 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:ditallop
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:ditallop
ID: 24454987
Fair enough!;-) Thanks for taking the time you did to answer the part of the question you could.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now