Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 25, 2003

XML and SQL 2000 (Part 1) - Page 2

By Marcin Policht

    • The EXPLICIT keyword gives you the most flexibility, but at the same time, it is the most complex. The SELECT statement itself is used to form the hierarchy of XML elements and attributes. In the most basic form, the first column (always an integer value labeled as Tag) specified in the statement indicates the current XML element. The second column (labeled as Parent) indicates which element is the parent of the current element (and can be set to 0 or NULL if the current element is the top one). Both values are referenced in the subsequent columns, (actual columns from the target table), to determine their relative placement. These columns are converted to XML elements or attributes using the following notation:
      Element!Tag!Attribute!Directive
      

      Where Element designates the arbitrarily chosen name of the XML element and Tag is the number of the element (as explained above). The meaning of Attribute depends on the value of Directive. If Directive is omitted, then Attribute is used as the name of the XML attribute: if Directive is set to the value element, then Attribute is used as the name of the XML element (Directive can have a number of other values - for more information refer to the SQL Books Online). This should become clearer with the help of a few examples. The following SELECT statement:

      SELECT  1 		AS Tag,
      	NULL		AS Parent,
      	ShipperID 	AS [ShippersElement!1!ShipperIDAttribute],
      	CompanyName	AS [ShippersElement!1!CompanyNameAttribute],
      	Phone		AS [ShippersElement!1!PhoneAttribute]
      FROM Shippers
      FOR XML EXPLICIT
      

      Produces the output in the form:

      <ShippersElement ShipperIDAttribute="1" CompanyNameAttribute="Speedy Express"
       PhoneAttribute="(503) 555-9831"/>
      <ShippersElement ShipperIDAttribute="2" CompanyNameAttribute="United Package"
       PhoneAttribute="(503) 555-3199"/>
      <ShippersElement ShipperIDAttribute="3" CompanyNameAttribute="Federal Shipping" 
      PhoneAttribute="(503) 555-9931"/>
      

      We could easily change the attributes in the output into elements by including the element directive:

      SELECT  1 		AS Tag,
      	NULL		AS Parent,
      	ShipperID 	AS [ShippersElement!1!ShipperIDAttribute!element],
      	CompanyName	AS [ShippersElement!1!CompanyNameAttribute!element],
      	Phone		AS [ShippersElement!1!PhoneAttribute!element]
      FROM Shippers
      FOR XML EXPLICIT
      

      Which would give us:

      <ShippersElement>
         <ShipperIDAttribute>1</ShipperIDAttribute>
         <CompanyNameAttribute>Speedy Express</CompanyNameAttribute>
         <PhoneAttribute>(503)555-9831</PhoneAttribute>
      </ShippersElement>
      <ShippersElement>
         <ShipperIDAttribute>2</ShipperIDAttribute>
         <CompanyNameAttribute>United Package</CompanyNameAttribute>
         <PhoneAttribute>(503) 555-3199</PhoneAttribute>
      </ShippersElement>
      <ShippersElement>
         <ShipperIDAttribute>3</ShipperIDAttribute>
         <CompanyNameAttribute>Federal Shipping</CompanyNameAttribute>
         <PhoneAttribute>(503) 555-9931</PhoneAttribute>
      </ShippersElement>
      

      You can get more creative by using multiple SELECT statements, combined together with the UNION ALL clause (note that this also requires an appropriately formed ORDER BY clause). For example:

      SELECT  1 		AS Tag,
      	NULL		AS Parent,
      	ShipperID 	AS [Shippers!1!ShipperID],
      	NULL		AS [Shippers!2!CompanyName]
      FROM Shippers
      UNION ALL
      SELECT 	2,
      	1,
      	ShipperID,
      	CompanyName
      FROM Shippers
      ORDER BY [Shippers!1!ShipperID],[Shippers!2!CompanyName]
      FOR XML EXPLICIT
      

      Which produces the following results:

      <Shippers ShipperID="1">
         <Shippers CompanyName="Speedy Express"/>
      </Shippers>
      <Shippers ShipperID="2">
         <Shippers CompanyName="United Package"/>
      </Shippers>
      <Shippers ShipperID="3">
         <Shippers CompanyName="Federal Shipping"/>
      </Shippers>
      
  • " XMLDATA specifies that, in addition to the XML document, its schema will be returned (at the beginning of the output). XML schema provides information about the structure of data contained in the XML document. The schema, in such case, reflects the definition of tables referenced in the SELECT statement. For example, adding it to one of our earlier examples:
    SELECT ShipperID, CompanyName, Phone
    FROM Shippers
    FOR XML RAW, XMLDATA
    

    Would produce, in addition to already presented results, the following:

    <Schema name="Schema4" xmlns="urn:schemas-microsoft-com:xml-data" 
    xmlns:dt="urn:schemas-microsoft-com:datatypes">
      <ElementType name="row" content="empty" model="closed">
        <AttributeType name="ShipperID" dt:type="i4"/>
        <AttributeType name="CompanyName" dt:type="string"/>
        <AttributeType name="Phone" dt:type="string"/>
        <attribute type="ShipperID"/>
        <attribute type="CompanyName"/>
        <attribute type="Phone"/>
      </ElementType>
    </Schema>
    
  • " Finally, BINARY BASE 64 is used to return the binary data in base64 encoded format, (obligatory when retrieving binary data in combination with RAW and EXPLICIT mode).

Information presented in this article should familiarize you with extracting data from a relational database in XML format. In the next article of this series I will describe the process of using OpenXML to create rowset based views of XML data, which can be subsequently manipulated as if they contained relational data.

» See All Articles by Columnist Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date