Solved

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

Posted on 2009-05-20
8
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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