Solved

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

Posted on 2009-05-20
8
530 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error message when scheduling a job using a linked Server 12 60
SQL Query 2 61
PERFORMANCE OF SQL QUERY 13 72
insert query with value having 's 2 51
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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