SQLUNINTERRUPTED

PATH mode was added in SQL 2005, to allow for easier query writing experience. Using the FOR XML EXPLICIT mode, we could have got the desired results, but writing query was really cumbersome. Especially if there is multiple level of nesting.

For a complete list of related articles refer here.

FOR PATH provides a very simple way for mixing elements and attributes. It also provides a simpler way of nesting elements in the XML. In PATH mode, column names are used to represent whether the values will form an Element, an attribute. If a column name or Column alias is not specified all values will be represented inline. For example,

SELECT ‘I dont have a Column Name’ FOR XML PATH
<<<<<<Output>>>>>>             <row>I dont have a Column Name</row>         <<<<<<Output>>>>>> 

SELECT ‘I dont have a Column Name’, ‘ Another Column’ FOR XML PATH
<<<<<<Output>>>>>>            <row>I dont have a Column Name Another Column</row>     <<<<<<Output>>>>>> 

Since we are not providing column names (which in turn determines the nesting, SQL tries to add everything as one attribute.

Specifying Column Names

Column names in FOR PATH mode, have special meaning. Care should be while specifying the column names. As mentioned below, different symbols or formats have different meaning in the PATH mode.

Column names starting with @ : Path mode will treat it as an attribute value. If there a path expression prior to the @ColName, then this column name will become an attribute of the Element specified by the path expression. Else it will become an attribute of the root element. For Example…

/Employee/@EmpId : This will make EmpId an attribute of Employee Element.
@EmpID: This will create EmpID as an Attribute of the root element.

Column names Not starting with @ : Path mode will treat it as an Element. If there a path expression prior to the ColName, then this column name will become an SubElement of the Element specified by the path expression. Else it will become a SubElement of the root element. For Example…

/Employee/Name: This will make Name as SubElement of Employee Element.
Name: This will create Name as a SubElement of the root element.

Columns with Same path prefix: Columns having the same path prefix are grouped together and created as SubElement of the Element specified. If for some reason a different column with a different prefix is specified in between a sequence, a new element will be created when the path prefix is repeated. For example,

SELECT 
       FirstName "EmpName/First",
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City "Address/City",
       MiddleName "EmpName/Middle",
       LastName "EmpName/Last"
FROM   HumanResources.EmployeeAddress E, Person.Contact C, Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID=1
FOR XML PATH

In the above query we have specified FirstName, MiddelName and LastName as having the path prefix "/EmpName/…", which causes SQL to try and club these together and create as SubElements of the EmpName element. But in the query above, we have specified "/Address/.." immediately after the FirstName column, which restricts SQL from grouping the "/EmpName" elements together. The output in this case will be

<row>
  <EmpName>
    <First>Gustavo</First>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <City>Monroe</City>
  </Address>
  <EmpName>
    <Last>Achong</Last>
  </EmpName>
</row>

Column Names as Wildcard characters: If wildcards are used in place of column names, SQL will treat it like Columns with no names.

Column Names with Path Specified as Data: If we have a nested query returning multiple value, or if we want to specify or create a list/union like element, we can use the data() path expressions. This will tell SQL Server that the element generated are to be treated as atomic values. If there are multiple values, then a space separated list of values is created. For Example,

create table test_xml_path(a int)
go
insert into test_xml_path values (10)
go 7 

SELECT 1       as "@First",
       2                 as "@Second",
       (select a as "data()" from test_xml_path FOR XML PATH (”)) as "@Third"
FOR XML PATH

— Output ———-
<row First="1" Second="2" Third="10 10 10 10 10 10 10" />

Now that we have listed all the conventions, lets try to run a query which will use some of the naming conventions and the directives.

SELECT
       EmployeeID "@EmpID",               
       FirstName "EmpName/First",
       MiddleName "EmpName/Middle",
       LastName "EmpName/Last",
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City "Address/City"
FROM   HumanResources.EmployeeAddress E, Person.Contact C, Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID=1
FOR XML PATH (‘Employee’), ELEMENTS XSINIL
go

— "@EmpID" Forms an attribute for root node.
— "EmpName/First" First forms a child element of the EmpName element. Similarly for "EmpName/Middle" and "EmpName/Last"
— Similarly AddrLine1,AddrLine2,City forms a SubElements for Address.
— The ELEMENTS and XSINIL directives have the same usage as with AUTO mode, EXPLICIT mode or RAW mode.

<<<<<<<<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Middle xsi:nil="true" />
    <Last>Achong</Last>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <AddrLIne2 xsi:nil="true" />
    <City>Monroe</City>
  </Address>
</Employee>

<<<<<<<<<<<<<<<<<<<<<<< Output >>>>>>>>>>>>

In the next post, I will discussing Nested FOR XML queries.