Solved

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

Posted on 2009-05-20
8
526 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

13 Experts available now in Live!

Get 1:1 Help Now