Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Table to XML using SCHEMA from XSD

From Wiki

Jump to: navigation, search

This will use an XSD file to generate a SCHEMA COLLECTION in SQL Server to export data from a table to an XML format. This can used in several methods to dump to XML files. It is one way and possibly not the most efficient given the amount of XML data you need to generate. Highly recommend testing this before throwing a large amount of data at it.

Sample required output would be http://www.w3schools.com/xml/simple.xml


  1. --Create test table relating to the breakfast menu
  2. CREATE TABLE Menus
  3. (name varchar(255)
  4. ,price numeric(12,4)
  5. ,description varchar(1500)
  6. ,calories int);
  7.  
  8. --insert some test data
  9. Insert Into Menus Values ('eggs',22.22,'test description eggs',333);
  10. Insert Into Menus Values ('steak',22.22,'test description steak',333);
  11. Insert Into Menus Values ('big side of bacon',22.22,'test description bacon',333);
  12. Insert Into Menus Values ('huge side of bacon',22.22,'test description more bacon',333);
  13. Insert Into Menus Values ('OMG BACON!',22.22,'test description oh man, more bacon',333);
  14.  
  15. /* XSD - schema used to define the export
  16. <?xml version="1.0"?>
  17. <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  18.   <xs:element name="breakfast_menu">
  19.     <xs:complexType>
  20.       <xs:sequence>
  21.         <xs:element minOccurs="0" maxOccurs="unbounded" name="food">
  22.           <xs:complexType>
  23.             <xs:sequence>
  24.               <xs:element minOccurs="0" name="name" type="xs:string" />
  25.               <xs:element minOccurs="0" name="price" type="xs:string" />
  26.               <xs:element minOccurs="0" name="description" type="xs:string" />
  27.               <xs:element minOccurs="0" name="calories" type="xs:unsignedShort" />
  28.             </xs:sequence>
  29.           </xs:complexType>
  30.         </xs:element>
  31.       </xs:sequence>
  32.     </xs:complexType>
  33.   </xs:element>
  34. </xs:schema>
  35. */
  36.  
  37. DECLARE @MenuSchema XML
  38. SET @MenuSchema =   (      
  39. SELECT * FROM OPENROWSET        
  40.     (BULK 'C:\Menu.xsd', SINGLE_CLOB)            
  41.         AS xmlData)
  42. --create the schema for using later in the XML type
  43. CREATE XML SCHEMA COLLECTION MenuSchema AS @MenuSchema;
  44.  
  45. DECLARE @xmlData XML (MenuSchema)
  46. SET @xmlData =
  47.             (SELECT
  48.                 [name]
  49.                 ,price
  50.                 ,[description]
  51.                 ,calories
  52.             FROM
  53.             dbo.Menus [food]
  54.             FOR XML AUTO, ROOT ('breakfast_menu'), ELEMENTS)
  55.  
  56. SELECT @xmlData

--onpnt 01:30, 13 May 2010 (GMT)

770 Rating: 2.8/5 (64 votes cast)