70-451–MCITP–Design an XML Strategy–Design a Query Strategy by using FOR XML

Requirements

Design a query strategy by using FOR XML.
This objective may include but is not limited to: views, FOR XML PATH and EXPLICIT, FOR XML…TYPE

My Take

This section appears to be concerned with how it is that we will be able to create XML from relational data. This will be an important task as XML is a prevalent technology used to pass information between systems, and it is imperative that we be able to produce this information in an efficient manner. As such, it is important that we learn the BASIC Syntax of the FOR XML Clause. I’m not quite sure why the bullet points call for the FOR XML PATH and EXPLICIT modes but not the AUTO or RAW modes.

For this section, as with all of the XML Sections, I have read Chapter 13 – XML and Xquery from Inside Microsoft SQL Server 2008: T-SQL Programming (Chapter by Sajal Dam) and watched the XML MCM Video. Although I have not read it, I believe that The Art of XSD SQL Server Schema Collections by Jacob Sebastian would be a good place to learn more about the XML Views using AXSD as well.

Views

XML Views

In the Understanding XML in SQL Server article the section on XML View Technology discusses a mapping between XML Schemas and tables in the database by creating an “XML View” using an Annotated XML Schema (AXSD). This view can then be used to query the data using the XPath language or to bulk load data into the relational tables.

An AXSD Schema describes an XML Document by defining the structure, types, order and constraints of that document. The AXSD maps the xml to the relational data using a well defined schema with the various XSD Annotations, such as sql:relation and sql:field. However, even without these annotations, the AXSD will generate Default Mapping of XSD Elements and Attributes to Tables and Columns.

Finally, there should be some discussion around the Security Considerations of AXSD , as the default mappings will expose the data structures as would the schema itself.

SQL Views with FOR XML Auto

When looking into the XML Views I saw a post discussing how normal sql views could be used with FOR XML AUTO to generate a different format of XML than if the query had been used on its’ own. Since this seemed in line with the objective of using FOX XML, I figured I would look into it a little more, even though I don’t believe that this is what was intended in light of the understanding xml in sql server article referenced above.

For XML Auto Multiple Join Query

/* NOTE: This query is taken directly from the AdventureWorks.HumanResources.vEmployee view */
SELECT TOP 2
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM
[HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
FOR XML AUTO;

For XML Auto Multiple Join Query Results

<e EmployeeID="161"JobTitle="Document Control Assistant"
<c FirstName="Karen"MiddleName="R"LastName="Berge"Phone="746-555-0164"EmailAddress="karen0@adventure-works.com"EmailPromotion="0"
  <a AddressLine1="3127 El Camino Drive"City="Index"PostalCode="98256"
   <sp StateProvinceName="Washington"
    <cr CountryRegionName="United States"/>
   </sp>
  </a>
</c>
</e>
<e EmployeeID="212"JobTitle="Control Specialist"
<c FirstName="Chris"MiddleName="K"LastName="Norred"Phone="575-555-0126"EmailAddress="chris1@adventure-works.com"EmailPromotion="0"
  <a AddressLine1="989 Crown Ct"City="Issaquah"PostalCode="98027"
   <sp StateProvinceName="Washington"
    <cr CountryRegionName="United States"/>
   </sp>
  </a>
</c>
</e>

For XML Auto View Query

SELECT TOP 2
*
FROM
[HumanResources].[vEmployee]
FOR XML AUTO

For XML Auto View Results

<HumanResources.vEmployee EmployeeID="265"Title="Ms."FirstName="Janice"MiddleName="M"LastName="Galvin"JobTitle="Tool Designer"Phone="473-555-0117"EmailAddress="janice0@adventure-works.com"EmailPromotion="2"AddressLine1="3397 Rancho View Drive"City="Redmond"StateProvinceName="Washington"PostalCode="98052"CountryRegionName="United States"/>
<HumanResources.vEmployee EmployeeID="159"FirstName="Shane"MiddleName="S"LastName="Kim"JobTitle="Production Supervisor - WC45"Phone="810-555-0178"EmailAddress="shane0@adventure-works.com"EmailPromotion="2"AddressLine1="9745 Bonita Ct."City="Bellevue"StateProvinceName="Washington"PostalCode="98004"CountryRegionName="United States"/>

FOR XML PATH and EXPLICIT

FOR XML PATH

The FOR XML PATH syntax allows for full control of how the output XML will be formatted. It uses column names and aliases as XPath expressions to define the path to the element or attribute to be output. With FOR XML PATH you define attributes with the @ symbol, and element hierarchies with a / symbol. If multiple columns are defined with the same base paths, and they are grouped together, they will be included in the same base node(s). However, if the columns are not grouped together the nodes will be split. If a column is left as just the column name, by default, it becomes an element under the base node. If the column is unnamed it is inserted “inline”. Similarly, if you give the wildcard (*) as the column name it is inserted inline as well. When multiple levels of nested hierarchies are required, you can use sub-queries. (See Columns with a Name)

One can also use the Xpath node tests as the column name. If text() is used, the column is added as a text node. If comment() is used, the column is added as an XML comment. If node() is used, the results will be mapped inline (the same as if no column name were given, or if the column name was a wildcard (*)). If processing-instructions(name) is used, the output will be returned as a processing instructions node. When data() is used, the value is treated as an atomic value. If the next value is also an atomic value a space will be added and the values will be concatenated.

By default a null value will not return the node. However, should this value be returned as an XML element node stating the null you should use the FOR XML PATH, ELEMENTS XSINIL option.

Finally, you can specify the namespaces to be used for the XML nodes by using the WITH XMLNAMESPACES() option at the beginning of the query and then mapping the individual elements to the appropriate namespace.

Sample For XML Path Simple Query

SELECT
Sales.SalesOrderHeader.SalesOrderID AS "@OrderID"
, Sales.SalesOrderHeader.OrderDate AS "@OrderDate"
, Sales.SalesOrderHeader.PurchaseOrderNumber AS [PO]
, Sales.SalesOrderHeader.CustomerID AS [PO/Customer]
FROM
Sales.SalesOrderHeader
WHERE
Sales.SalesOrderHeader.SalesOrderID BETWEEN 43659
AND 43661
FOR XML PATH
;

Sample For XML Path Simple Results

<row OrderID="43659"OrderDate="2001-07-01T00:00:00"
<PO>PO522145787<Customer>676</Customer></PO>
</row>
<row OrderID="43660"OrderDate="2001-07-01T00:00:00"
<PO>PO18850127500<Customer>117</Customer></PO>
</row>
<row OrderID="43661"OrderDate="2001-07-01T00:00:00"
<PO>PO18473189620<Customer>442</Customer></PO>
</row>

Sample For XML Path with Sub-Query

SELECT
Sales.SalesOrderHeader.SalesOrderID AS "@OrderID"
, Sales.SalesOrderHeader.OrderDate AS "@OrderDate"
, Sales.SalesOrderHeader.PurchaseOrderNumber AS [PO]
, Sales.SalesOrderHeader.CustomerID AS [PO/Customer]
, (
  SELECT
   Sales.SalesOrderDetail.SalesOrderDetailID AS "@ID"
   , Sales.SalesOrderDetail.LineTotal AS "@LineTotal"
  FROM
   Sales.SalesOrderDetail
  WHERE
   Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
  FOR XML PATH ('Detail'), TYPE
)
FROM
Sales.SalesOrderHeader
WHERE
Sales.SalesOrderHeader.SalesOrderID BETWEEN 43659
AND 43661
FOR XML PATH ('Sale'), ROOT ('Sales'), TYPE
;

Sample For XML Path with Sub-Query Results

<Sales>
<Sale OrderID="43659"OrderDate="2001-07-01T00:00:00"
<PO>PO522145787<Customer>676</Customer></PO>
<Detail ID="1"LineTotal="2024.994000"/>
<Detail ID="2"LineTotal="6074.982000"/>
<Detail ID="3"LineTotal="2024.994000"/>
<Detail ID="4"LineTotal="2039.994000"/>
<Detail ID="5"LineTotal="2039.994000"/>
<Detail ID="6"LineTotal="4079.988000"/>
<Detail ID="7"LineTotal="2039.994000"/>
<Detail ID="8"LineTotal="86.521200"/>
<Detail ID="9"LineTotal="28.840400"/>
<Detail ID="10"LineTotal="34.200000"/>
<Detail ID="11"LineTotal="10.373000"/>
<Detail ID="12"LineTotal="80.746000"/>
</Sale>
<Sale OrderID="43660"OrderDate="2001-07-01T00:00:00"
<PO>PO18850127500<Customer>117</Customer></PO>
<Detail ID="13"LineTotal="419.458900"/>
<Detail ID="14"LineTotal="874.794000"/>
</Sale>
<Sale OrderID="43661"OrderDate="2001-07-01T00:00:00"
<PO>PO18473189620<Customer>442</Customer></PO>
<Detail ID="15"LineTotal="809.760000"/>
<Detail ID="16"LineTotal="714.704300"/>
<Detail ID="17"LineTotal="1429.408600"/>
<Detail ID="18"LineTotal="20.746000"/>
<Detail ID="19"LineTotal="115.361600"/>
<Detail ID="20"LineTotal="1445.189800"/>
<Detail ID="21"LineTotal="6074.982000"/>
<Detail ID="22"LineTotal="4049.988000"/>
<Detail ID="23"LineTotal="40.373000"/>
<Detail ID="24"LineTotal="1637.400000"/>
<Detail ID="25"LineTotal="8099.976000"/>
<Detail ID="26"LineTotal="4079.988000"/>
<Detail ID="27"LineTotal="57.680800"/>
<Detail ID="28"LineTotal="4049.988000"/>
<Detail ID="29"LineTotal="100.932500"/>
</Sale>
</Sales>

FOR XML EXPLICIT

The FOR XML EXPLICIT is really only used for backwards compatibility, as it is a much more complex and cumbersome method of returning data. Like the FOR XML PATH syntax, FOR XML EXPLICIT is another method of gaining complete control over the XML returned. With the EXPLICIT mode, you use UNION ALL statement to return the records in the appropriate hierarchy. You return two integer columns, Tag and Parent, which define where in the hierarchy the element will be returned. With EXPLICIT, you define the element and attribute names with column aliases using the ElementName!TagNumber!AttributeName!Directive Format.

Sample For XML Explicit Query

SELECT
1 AS Tag
, NULL AS Parent
, Sales.SalesOrderHeader.SalesOrderID AS [Sale!1!OrderID]
, Sales.SalesOrderHeader.OrderDate AS [Sale!1!OrderDate]
, NULL AS [Detail!2!DetailID]
, NULL AS [Detail!2!LineTotal]
FROM
Sales.SalesOrderHeader
WHERE
Sales.SalesOrderHeader.SalesOrderID BETWEEN 43659
AND 43661
UNION ALL
SELECT
2 AS Tag
, 1 AS Parent
, Sales.SalesOrderHeader.SalesOrderID AS [Sale!1!OrderID]
, NULL AS [Sale!1!OrderDate]
, Sales.SalesOrderDetail.SalesOrderDetailID AS [Detail!2!DetailID]
, Sales.SalesOrderDetail.LineTotal AS [Detail!2!LineTotal]
FROM
Sales.SalesOrderHeader
INNER JOIN
Sales.SalesOrderDetail
ON
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
WHERE
Sales.SalesOrderHeader.SalesOrderID BETWEEN 43659
AND 43661
ORDER BY
[Sale!1!OrderID]
, [Detail!2!DetailID]
FOR XML EXPLICIT

Sample For XML Explicit Output

<Sale OrderID="43659"OrderDate="2001-07-01T00:00:00"
<Detail DetailID="1"LineTotal="2024.994000"/>
<Detail DetailID="2"LineTotal="6074.982000"/>
<Detail DetailID="3"LineTotal="2024.994000"/>
<Detail DetailID="4"LineTotal="2039.994000"/>
<Detail DetailID="5"LineTotal="2039.994000"/>
<Detail DetailID="6"LineTotal="4079.988000"/>
<Detail DetailID="7"LineTotal="2039.994000"/>
<Detail DetailID="8"LineTotal="86.521200"/>
<Detail DetailID="9"LineTotal="28.840400"/>
<Detail DetailID="10"LineTotal="34.200000"/>
<Detail DetailID="11"LineTotal="10.373000"/>
<Detail DetailID="12"LineTotal="80.746000"/>
</Sale>
<Sale OrderID="43660"OrderDate="2001-07-01T00:00:00"
<Detail DetailID="13"LineTotal="419.458900"/>
<Detail DetailID="14"LineTotal="874.794000"/>
</Sale>
<Sale OrderID="43661"OrderDate="2001-07-01T00:00:00"
<Detail DetailID="15"LineTotal="809.760000"/>
<Detail DetailID="16"LineTotal="714.704300"/>
<Detail DetailID="17"LineTotal="1429.408600"/>
<Detail DetailID="18"LineTotal="20.746000"/>
<Detail DetailID="19"LineTotal="115.361600"/>
<Detail DetailID="20"LineTotal="1445.189800"/>
<Detail DetailID="21"LineTotal="6074.982000"/>
<Detail DetailID="22"LineTotal="4049.988000"/>
<Detail DetailID="23"LineTotal="40.373000"/>
<Detail DetailID="24"LineTotal="1637.400000"/>
<Detail DetailID="25"LineTotal="8099.976000"/>
<Detail DetailID="26"LineTotal="4079.988000"/>
<Detail DetailID="27"LineTotal="57.680800"/>
<Detail DetailID="28"LineTotal="4049.988000"/>
Detail DetailID="29"LineTotal="100.932500"/>
</Sale>

FOR XML … TYPE

The TYPE Directive in FOR XML Queries is used to return the XML data as an xml data type. It can be used in any of the FOR XML queries and is an option for the Basic FOR XML syntax. When this option is used, the output can be assigned to a variable of type xml, inserted into an xml column, etc.

Advertisements
This entry was posted in SQL and tagged , , . Bookmark the permalink.

3 Responses to 70-451–MCITP–Design an XML Strategy–Design a Query Strategy by using FOR XML

  1. Mansi says:

    In FOR XML PATH with subquery, if the subquery does not return any result, then what can we do to display a blank node in XML?

    • Eric Wisdahl says:

      You could set the ELEMENTS XSINIL directive on the outer query. However, this will bring the data back in a little bit different format.

      SELECT
      [ConfigurationFilter] as “@ConfigurationFilter”
      ,(
      SELECT
      [ConfiguredValue]
      , [PackagePath]
      , [ConfiguredValueType]
      FROM
      [Configuration].[SSISConfigurations] as InnerConfigurations
      WHERE
      InnerConfigurations.ConfigurationFilter = SSISConfigurations.ConfigurationFilter
      AND 1 = 2
      FOR XML PATH (‘ConfigurationDetails’), TYPE
      ) as ConfigurationDetails
      FROM
      (
      SELECT DISTINCT
      ConfigurationFilter
      FROM
      [Configuration].[SSISConfigurations]
      ) as SSISConfigurations
      FOR XML PATH (‘Configuration’), ROOT (‘Configurations’), ELEMENTS XSINIL, TYPE
      GO

  2. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s