XML and SQL 2000 (Part 1) - Page 2

• 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