Click here to monitor SSC
  • Av rating:
  • Total votes: 393
  • Total comments: 29
Robert Sheldon

Using the FOR XML Clause to Return Query Results as XML

27 May 2009

The FOR XML clause in SQL Server causes a lot of difficulty, mainly because it is rather poorly explained in Books-on-Line. We challenged Bob Sheldon to make it seem simple. Here is his sublime response.

SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.

When you include the FOR XML clause in your query, you must specify one of the four supported modes—RAW, AUTO, EXPLICIT, or PATH. The options available to each mode vary according to that mode; however, many of the options are shared among the modes. In this article, I explain how to use each of these modes to retrieve data as XML and provide examples that demonstrate how they use the various options.

The RAW Mode

The RAW mode generates a single XML element for each row in the result set returned by the query.

To use the FOR XML clause in RAW mode, you simply append the clause and RAW keyword to your SELECT statement, as shown in the following example:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW;

Notice that the SELECT statement itself is a very basic query. (The statement pulls data from the AdventureWorks sample database.) Without the FOR XML clause, the statement would return the following results:

EmployeeID  FirstName  MiddleName  LastName

----------  ---------  ----------  --------

4           Rob        NULL        Walters

168         Rob        T           Caron

With the addition of the FOR XML clause, the statement returns the data as the following XML:

<row EmployeeID="4" FirstName="Rob" LastName="Walters" />

<row EmployeeID="168" FirstName="Rob" MiddleName="T" LastName="Caron" />

As you can see, each <row> element maps to a row that is returned by the SELECT statement, and each column, by default, is treated as an attribute of that element.

Note: You can include a FOR XML clause only in SELECT statements, if those statements define the outer, or top-level, query. However, you can also include the clause in INSERT, UPDATE, and DELETE statements that are part of a subquery.

In the preceding example, each element in the XML is named <row> by default. However, you can override the default behavior by providing a name for the element, as the following example shows:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee');

Now the element associated with each row returned by the query will be named <Employee>, rather than the default <row>:

<Employee EmployeeID="4" FirstName="Rob" LastName="Walters" />

<Employee EmployeeID="168" FirstName="Rob" MiddleName="T" LastName="Caron" />

In addition to being able to provide a name for the row element, you can also specify that a root element be created to wrap all other elements. To create a root element, add the ROOT keyword to your FOR XML clause:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT;

Notice that you must include a comma when adding an option such as ROOT in order to separate the elements. As the following results show, a <root> element is now included in the XML:

<root>

  <Employee EmployeeID="4" FirstName="Rob" LastName="Walters" />

  <Employee EmployeeID="168" FirstName="Rob" MiddleName="T" LastName="Caron" />

</root>

As with the row element, you can also provide a specific name for the root element:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees');

In this case, I’ve named the root element <Employees>, as shown in the following results:

<Employees>

  <Employee EmployeeID="4" FirstName="Rob" LastName="Walters" />

  <Employee EmployeeID="168" FirstName="Rob" MiddleName="T" LastName="Caron" />

</Employees>

Up to this point, the examples I’ve shown you have added column values as attributes to each row element. This is the default behavior of the RAW mode. However, you can instead specify that the column values be added as child elements to the row element by including the ELEMENTS option in the FOR XML clause:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS;

Once again, I’ve added a comma to separate the options. As you can see in the following results, each <Employee> element now includes a set of child elements that correspond to the columns returned by the query:

<Employees>

  <Employee>

    <EmployeeID>4</EmployeeID>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </Employee>

  <Employee>

    <EmployeeID>168</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

Now the <Employee> elements no longer include any attributes and all data is rendered through individual child elements.

If you refer back to the XML returned by the previous example, you’ll notice that the data for employee 4 (Rob Walters) does not include a middle name. This is because that MiddleName value is null in the source data, and by default, no elements are created for a column whose value is null. However, you can override this behavior by adding the XSINIL keyword to the ELEMENTS option:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Now the results will include an element for the MiddleName column and will include the xsi:nil attribute with a value of true when a value is null, as shown in the following XML:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee>

    <EmployeeID>4</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Walters</LastName>

  </Employee>

 <Employee>

    <EmployeeID>168</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

Notice that the xmlns:xsi attribute has also been added to the root node and provides the name of the default schema instance.

Another important option that is supported by the RAW node is XMLSCHEMA, which specifies that an inline W3C XML Schema (XSD) be included in the XML data. You add the XMLSCHEMA option in the same way you add other options:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL, XMLSCHEMA;

As you can see in the following results, the schema is fully defined and is incorporated in the XML results:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">

    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

    <xsd:element name="Employee">

      <xsd:complexType>

        <xsd:sequence>

          <xsd:element name="EmployeeID" type="sqltypes:int" nillable="1" />

          <xsd:element name="FirstName" nillable="1">

            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">

              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

                <xsd:maxLength value="50" />

              </xsd:restriction>

            </xsd:simpleType>

          </xsd:element>

          <xsd:element name="MiddleName" nillable="1">

            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">

              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

                <xsd:maxLength value="50" />

              </xsd:restriction>

            </xsd:simpleType>

          </xsd:element>

          <xsd:element name="LastName" nillable="1">

            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">

              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

                <xsd:maxLength value="50" />

              </xsd:restriction>

            </xsd:simpleType>

          </xsd:element>

        </xsd:sequence>

      </xsd:complexType>

    </xsd:element>

  </xsd:schema>

  <Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">

    <EmployeeID>4</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Walters</LastName>

  </Employee>

  <Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">

    <EmployeeID>168</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

When you specify that a schema be created, you can also specify the name of the target namespace. For example, the following FOR XML clause includes the XMLSCHEMA option, followed by the name of the target namespace (urn:schema_example.com):

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL,

   XMLSCHEMA ('urn:schema_example.com');

The statement will return the same results as the previous example, except that the XML will now include the new name of the target namespace.

The SELECT statements shown in the preceding examples have retrieved data from non-XML columns (in this case, integer and string columns). However, your queries might also retrieve data from XML columns. In such cases, the FOR XML clause will incorporate the data retrieved from an XML column into the XML result set.

For example, the following SELECT statement uses the XML query() method to retrieve education-related data from the Resume column in the JobCandidate table:

SELECT e.EmployeeID, c.FirstName, c.LastName,

  jc.Resume.query('declare namespace ns=

     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

     /ns:Resume/ns:Education')

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

   INNER JOIN HumanResources.JobCandidate jc

   ON e.EmployeeID = jc.EmployeeID

WHERE e.EmployeeID = 268

FOR XML RAW ('Employee'), ELEMENTS;

The query() method itself retrieves the following data from the Resume column:

<ns:Education xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">

  <ns:Edu.Level>Bachelor</ns:Edu.Level>

  <ns:Edu.StartDate>1986-09-15Z</ns:Edu.StartDate>

  <ns:Edu.EndDate>1990-05-20Z</ns:Edu.EndDate>

  <ns:Edu.Degree>Bachelor of Arts and Science</ns:Edu.Degree>

  <ns:Edu.Major>Business</ns:Edu.Major>

  <ns:Edu.Minor />

  <ns:Edu.GPA>3.3</ns:Edu.GPA>

  <ns:Edu.GPAScale>4</ns:Edu.GPAScale>

  <ns:Edu.School>Louisiana Business College of New Orleans</ns:Edu.School>

  <ns:Edu.Location>

    <ns:Location>

      <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>

      <ns:Loc.State>LA</ns:Loc.State>

      <ns:Loc.City>New Orleans</ns:Loc.City>

    </ns:Location>

  </ns:Edu.Location>

</ns:Education>

This data is incorporated into the rest of the result set when you use the FOR XML clause, as shown in the following results:

<Employee>

  <EmployeeID>268</EmployeeID>

  <FirstName>Stephen</FirstName>

  <LastName>Jiang</LastName>

  <ns:Education xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">

    <ns:Edu.Level>Bachelor</ns:Edu.Level>

    <ns:Edu.StartDate>1986-09-15Z</ns:Edu.StartDate>

    <ns:Edu.EndDate>1990-05-20Z</ns:Edu.EndDate>

    <ns:Edu.Degree>Bachelor of Arts and Science</ns:Edu.Degree>

    <ns:Edu.Major>Business</ns:Edu.Major>

    <ns:Edu.Minor />

    <ns:Edu.GPA>3.3</ns:Edu.GPA>

    <ns:Edu.GPAScale>4</ns:Edu.GPAScale>

    <ns:Edu.School>Louisiana Business College of New Orleans</ns:Edu.School>

    <ns:Edu.Location>

      <ns:Location>

        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>

        <ns:Loc.State>LA</ns:Loc.State>

        <ns:Loc.City>New Orleans</ns:Loc.City>

      </ns:Location>

    </ns:Edu.Location>

  </ns:Education>

</Employee>

As you can see, the <ns:Education> element and its child elements have been added to the XML data. The namespace defined on the source data in the XML column is also included.

The AUTO Mode

The AUTO mode in a FOR XML clause is slightly different from the RAW mode in the way that it generates the XML result set. The AUTO mode generates the XML by using heuristics based on how the SELECT statement is defined. The best way to understand how this works is to look at an example. The following SELECT statement, as in the previous examples, retrieves employee data from the AdventureWorks database:

SELECT Employee.EmployeeID, ContactInfo.FirstName,

   ContactInfo.MiddleName, ContactInfo.LastName

FROM HumanResources.Employee AS Employee

   INNER JOIN Person.Contact AS ContactInfo

   ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees');

Notice that I’ve provided meaningful alias names to the tables (Employee and Contact info). These names are used in defining the XML element names, so you’ll want to construct your SELECT statements accordingly. Now take a look at the results returned by this query:

<Employees>

  <Employee EmployeeID="4">

    <ContactInfo FirstName="Rob" LastName="Walters" />

  </Employee>

  <Employee EmployeeID="168">

    <ContactInfo FirstName="Rob" MiddleName="T" LastName="Caron" />

  </Employee>

</Employees>

As you can see, the <Employee> element has been named automatically based on the table alias name. Notice too that the <ContactInfo> element is a child element of <Employee>. The structure of the elements is based on the order in which the columns are defined in the SELECT list and the tables that are specified in the FROM clause. In this case, because EmployeeID is the first column in the SELECT list and the Employee table is included in the FROM clause, the first element is <Employee>. And because the remaining columns, which are associated with the ContactInfo table, appear next in the SELECT list, they are added as a child element. If an additional table and its columns were included in the SELECT list, after the other columns, they would appear as a child element of <ContactInfo>.

In addition, the columns and their values are added as attributes to the table-related elements. This structure is similar to what you saw in the RAW mode examples. And in the same way, you can override the default behavior by using the ELEMENTS option:

SELECT Employee.EmployeeID, ContactInfo.FirstName,

   ContactInfo.MiddleName, ContactInfo.LastName

FROM HumanResources.Employee AS Employee

   INNER JOIN Person.Contact AS ContactInfo

   ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS;

As you can see in the following XML result set, the column values are now included as child elements, rather than attributes:

<Employees>

  <Employee>

    <EmployeeID>4</EmployeeID>

    <ContactInfo>

      <FirstName>Rob</FirstName>

      <LastName>Walters</LastName>

    </ContactInfo>

  </Employee>

 <Employee>

    <EmployeeID>168</EmployeeID>

    <ContactInfo>

      <FirstName>Rob</FirstName>

      <MiddleName>T</MiddleName>

      <LastName>Caron</LastName>

    </ContactInfo>

  </Employee>

</Employees>

Notice that the <ContactInfo> element also contains child elements, one for each column.

If you want to include an element for columns with null values, you can use the XSINIL option, as you saw when using the RAW mode:

SELECT ContactInfo.FirstName, ContactInfo.MiddleName,

   ContactInfo.LastName, Employee.EmployeeID

FROM HumanResources.Employee AS Employee

   INNER JOIN Person.Contact AS ContactInfo

   ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

Now the results will include all elements. That means, if a value is null, the xsi:nil attribute is included:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Walters</LastName>

    <Employee>

      <EmployeeID>4</EmployeeID>

    </Employee>

  </ContactInfo>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

    <Employee>

      <EmployeeID>168</EmployeeID>

    </Employee>

  </ContactInfo>

</Employees>

As you’ve seen in these examples, the XML is based on how the columns are listed in the SELECT list. However, as I mentioned earlier, the XML is also based on the tables listed in the FROM clause. In the preceding examples, the SELECT list contained only columns that are referenced in the FROM clause. If a column is not directly associated with a table in the FROM clause (as in a computed or aggregate column), the column is nested at the deepest level wherever it appears.

For example, the following SELECT statement includes the FullName computed column, which concatenates the first and last names:

SELECT Employee.EmployeeID,

   (ContactInfo.FirstName + ' ' + ContactInfo.LastName) AS FullName,

   ContactInfo.EmailAddress

FROM HumanResources.Employee AS Employee

   INNER JOIN Person.Contact AS ContactInfo

   ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

Because the FullName column appears in the SELECT list after the EmployeeID column, the FullName column is added as a child element of <Employee>, as shown in the following XML:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee>

    <EmployeeID>4</EmployeeID>

    <FullName>Rob Walters</FullName>

    <ContactInfo>

      <EmailAddress>rob0@adventure-works.com</EmailAddress>

    </ContactInfo>

  </Employee>

  <Employee>

    <EmployeeID>168</EmployeeID>

    <FullName>Rob Caron</FullName>

    <ContactInfo>

      <EmailAddress>rob1@adventure-works.com</EmailAddress>

    </ContactInfo>

  </Employee>

</Employees>

As I’ve mentioned, the placement of columns in the SELECT list impacts the resulting XML. This is also the case with computed columns. For example, in the following SELECT statement, I’ve added the FullName column after the EmailAddress column:

SELECT Employee.EmployeeID, ContactInfo.EmailAddress,

   (ContactInfo.FirstName + ' ' + ContactInfo.LastName) AS FullName

FROM HumanResources.Employee AS Employee

   INNER JOIN Person.Contact AS ContactInfo

   ON ContactInfo.ContactID = Employee.ContactID

WHERE ContactInfo.FirstName = 'Rob'

FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL;

Now the FullName column will be added as a child element to the <ContactInfo> element, as the following XML demonstrates.

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee>

    <EmployeeID>4</EmployeeID>

    <ContactInfo>

      <EmailAddress>rob0@adventure-works.com</EmailAddress>

      <FullName>Rob Walters</FullName>

    </ContactInfo>

  </Employee>

  <Employee>

    <EmployeeID>168</EmployeeID>

    <ContactInfo>

      <EmailAddress>rob1@adventure-works.com</EmailAddress>

      <FullName>Rob Caron</FullName>

    </ContactInfo>

  </Employee>

</Employees>

As these results show, you must be aware of the order you place columns when you define your SELECT list.

Now let’s take a look at another aspect of the AUTO mode. One of the limitations of this mode (as well as the RAW mode) is that the column data is added as either attributes or child elements, depending on whether you specify the ELEMENTS option. However, there might be times when you want to return some of the data as attributes and some as child elements. One method you can use with the AUTO mode is to return some of the data in a subquery. For example, the following SELECT statement includes a subquery that returns the employee’s first and last names:

SELECT EmployeeID, LoginID,

   (SELECT FirstName, LastName

    FROM Person.Contact AS EmployeeName

    WHERE EmployeeName.ContactID = Employee.ContactID

    FOR XML AUTO, TYPE, ELEMENTS)

FROM HumanResources.Employee AS Employee

WHERE EmployeeID = 168

FOR XML AUTO;

Notice that the subquery includes a FOR XML clause that uses AUTO mode and includes the ELEMENTS option. The FOR XML clause also includes the TYPE option, which specifies that the data returned by the subquery be returned as the XML type. You must include the TYPE option to preserve the data as XML in the outer SELECT statement.

The outer SELECT statement also includes a FOR XML clause, but the ELEMENTS option is not included. As a result, only the first and last names will be returned as child elements, but the employee ID and login ID will be returned as attributes, as shown in the following XML:

<Employee EmployeeID="168" LoginID="adventure-works\rob1">

  <EmployeeName>

    <FirstName>Rob</FirstName>

    <LastName>Caron</LastName>

  </EmployeeName>

</Employee>

As you can see, subqueries let you maintain some control over the output. However, the AUTO mode (and the RAW mode, for that matter) provides little control over the XML returned by your query. For greater control, you’ll want to use the EXPLICIT mode or the PATH mode.

The EXPLICIT Mode

The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTO modes. To use this mode, you must build your SELECT statements in such as way as to define the XML hierarchy and structure. In addition, you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.

There are a number of rules that describe how to define your SELECT statements when using the EXPLICIT mode, and it is beyond the scope of this article to review all those rules, so be sure to refer to the topic “Using EXPLICIT Mode” in SQL Server Books Online for the details about how to construct your SELECT statements. In the meantime, let’s take a look at a few examples that help demonstrate some of the basic elements of the EXPLICIT mode.

When constructing your SELECT statement, you must include two columns in your SELECT list that describe the XML hierarchy. The first column, Tag, is assigned a numerical value for each level of the hierarchy. For instance, the first SELECT statement should include a Tag column with a value of 1. This is the top level of the hierarchy. The second SELECT statement should include a Tag column with a value of 2, and so on.

The second column that you should include in your SELECT statement is Parent. Again, this is a numerical value that identifies the parent of the hierarchy based on the Tag values you’ve assigned. In the first SELECT statement, the Parent value should be null to indicate that this is a top level hierarchy.

Your first SELECT statement should also include a reference to all the columns that will make up the XML structure. The columns must also include aliases that define that structure. Let’s look at an example to help understand how this all works. The following SELECT statements return results similar to what you’ve seen in previous examples; however, the SELECT statements themselves are more detailed:

SELECT 1 AS Tag,

   NULL AS Parent,

   e.EmployeeID AS [Employee!1!EmployeeID],

   NULL AS [ContactInfo!2!FirstName!ELEMENT],

   NULL AS [ContactInfo!2!MiddleName!ELEMENT],

   NULL AS [ContactInfo!2!LastName!ELEMENT]

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

UNION ALL

SELECT 2 AS Tag,

   1 AS Parent,

   e.EmployeeID,

   c.FirstName,

   c.MiddleName,

   c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON e.ContactID = c.ContactID

WHERE c.FirstName = 'Rob'

ORDER BY [Employee!1!EmployeeID], [ContactInfo!2!FirstName!ELEMENT]

FOR XML EXPLICIT;

In the first SELECT statement, I begin by defining the Tag column and assigning a value of 1 to that column. Next I define the Parent column and assign a null value. I then define the EmployeeID column and assign an alias to that column. Notice that I use a very specific structure to define the alias name:

<ElementName>!<TagNumber>!<AttributeName>[!<OptionalDirective>]

As the syntax shows, the first three components are required, and the last is optional:

  • <ElementName>: The name of the element that the value should be assigned to.
  • <TagNumber>: The tag number associated with the hierarchy that the value should be assigned to, as defined in the Tag column.
  • <AttributeName>: The name of the attribute associated with the column value, unless an optional directive is specified. For example, if the ELEMENT directive is specified, <AttributeName> is the name of the child element.
  • <OptionalDirective>: Additional information for how to construct the XML.

For example, based on the alias name assigned to the EmployeeID column, you can see that the EmployeeID attribute will be associated with the <Employee> element on the first level of the hierarchy.

Because the next three columns in the SELECT list are associated with the second level of the XML hierarchy, which is defined in the second SELECT statement, null values are assigned to the alias names for the column. This will provide the XML structure necessary to join the two SELECT statements.

The second SELECT statement is much simpler, but it still includes the Tag and Parent columns in the SELECT list. The remaining columns in the SELECT list are defined as you would normally define columns in your query.

The result set for the two SELECT statements is then ordered by the EmployeeID and FirstName columns. This is necessary so that null values appear first in the result set to ensure that the XML is properly formatted. The FOR XML clause is then appended to the end of the SELECT statement in order to generate the following XML:

<Employee EmployeeID="4">

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </ContactInfo>

</Employee>

<Employee EmployeeID="168">

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </ContactInfo>

</Employee>

The EmployeeID column has now been added as an attribute to the <Employee> element. However, you can change the EmployeeID column to a child element simply by adding the ELEMENT directive, as I did with the other columns:

SELECT 1 AS Tag,

   NULL AS Parent,

   e.EmployeeID AS [Employee!1!EmployeeID!ELEMENT],

   NULL AS [ContactInfo!2!FirstName!ELEMENT],

   NULL AS [ContactInfo!2!MiddleName!ELEMENT],

   NULL AS [ContactInfo!2!LastName!ELEMENT]

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

UNION ALL

SELECT 2 AS Tag,

   1 AS Parent,

   e.EmployeeID,

   c.FirstName,

   c.MiddleName,

   c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON e.ContactID = c.ContactID

WHERE c.FirstName = 'Rob'

ORDER BY [Employee!1!EmployeeID!ELEMENT], [ContactInfo!2!FirstName!ELEMENT]

FOR XML EXPLICIT;

Now the EmployeeID value will be displayed as a child element of <Employee>,the first level element:

<Employee>

  <EmployeeID>4</EmployeeID>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </ContactInfo>

</Employee>

<Employee>

  <EmployeeID>168</EmployeeID>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </ContactInfo>

</Employee>

You can also ensure that columns with null values will still display the element by changing the ELEMENTS directive to ELEMENTSXSINIL, as shown in the following SELECT statement:

SELECT 1 AS Tag,

   NULL AS Parent,

   e.EmployeeID AS [Employee!1!EmployeeID!ELEMENT],

   NULL AS [ContactInfo!2!FirstName!ELEMENT],

   NULL AS [ContactInfo!2!MiddleName!ELEMENTXSINIL],

   NULL AS [ContactInfo!2!LastName!ELEMENT]

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

UNION ALL

SELECT 2 AS Tag,

   1 AS Parent,

   e.EmployeeID,

   c.FirstName,

   c.MiddleName,

   c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON e.ContactID = c.ContactID

WHERE c.FirstName = 'Rob'

ORDER BY [Employee!1!EmployeeID!ELEMENT], [ContactInfo!2!FirstName!ELEMENT]

FOR XML EXPLICIT;

Now the results will include the xsi:nil attribute where values are null in the MiddleName column, as shown in the following XML:

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <EmployeeID>4</EmployeeID>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Walters</LastName>

  </ContactInfo>

</Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <EmployeeID>168</EmployeeID>

  <ContactInfo>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </ContactInfo>

</Employee>

As you can see from these examples, the EXPLICIT mode can cause your SELECT statements to become quite complex, especially if you want to add more levels to the hierarchy or want to create more intricate SELECT statements.  Fortunately, most of what you can do with the EXPLICIT mode, you can do with the PATH mode, and do it in a much simpler way.

The PATH Mode

When you specify the PATH mode in the FOR XML clause, column names (or their aliases) are treated as XPath expressions that determine how the data values will be mapped to the XML result set. By default, XML elements are defined based on column names. You can modify the default behavior by using the at (@) symbol to define attributes or the forward slash (/) to define the hierarchy. Let’s take a look at a few examples to demonstrate how all this works.

We’ll begin with the PATH mode’s default behavior. The following example includes a FOR XML clause that specifies only the PATH option:

SELECT e.EmployeeID, c.FirstName,

   c.MiddleName, c.LastName

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH;

Because no specific attributes or hierarchies have been defined, the query will return the following XML:

<row>

  <EmployeeID>4</EmployeeID>

  <FirstName>Rob</FirstName>

  <LastName>Walters</LastName>

</row>

<row>

  <EmployeeID>168</EmployeeID>

  <FirstName>Rob</FirstName>

  <MiddleName>T</MiddleName>

  <LastName>Caron</LastName>

</row>

As you can see, each column is added as a child element to the <row> element. You do not have to specify the ELEMENTS directive because individual elements are returned by default, based on the column names.

You can also rename the row element and define a root element, as you’ve seen in earlier examples:

SELECT e.EmployeeID, c.FirstName,

   c.MiddleName, c.LastName

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees');

As the following results show, the XML now includes the <Employees> root element and the individual <Employee> row elements:

<Employees>

  <Employee>

    <EmployeeID>4</EmployeeID>

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </Employee>

  <Employee>

    <EmployeeID>168</EmployeeID>

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

Suppose, now, that you want to include the EmployeeID value as an attribute of <Employee>.You can easily do this by adding an alias to the EmployeeID column in the SELECT clause and preceding the alias name with @, as shown in the following example:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees');

Now the <Employee>elements contain the EmpID attribute, along with the employee ID:

<Employees>

  <Employee EmpID="4">

    <FirstName>Rob</FirstName>

    <LastName>Walters</LastName>

  </Employee>

  <Employee EmpID="168">

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

You can see how easy it is to return both attributes and child elements by using the PATH mode. And if you want to include elements with null values, you simply include the ELEMENTS XSINIL option in your FOR XML clause:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Now your results include the xsi:nil attribute for those fields that contain null values:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee EmpID="4">

    <FirstName>Rob</FirstName>

    <MiddleName xsi:nil="true" />

    <LastName>Walters</LastName>

  </Employee>

  <Employee EmpID="168">

    <FirstName>Rob</FirstName>

    <MiddleName>T</MiddleName>

    <LastName>Caron</LastName>

  </Employee>

</Employees>

As you can see, the xsi:nil attribute in the <MiddleName> element has been set to true.

Note: Because the PATH mode automatically returns values as individual child elements, the ELEMENTS directive has no effect when used by itself in a FOR XML clause. It is only when the XSINIL option is also specified that the ELEMENTS directive adds value to the clause.

In addition to defining attributes within your column aliases in the SELECT list, you can also define hierarchies. You define hierarchies by using the forward slash and specifying the element names. For example, the following SELECT defines the <EmployeeName> element and its three child elements: <FirstName>, <MiddleName>, and <LastName>:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName AS "EmployeeName/FirstName",

   c.MiddleName AS "EmployeeName/MiddleName",

   c.LastName AS "EmployeeName/LastName"

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

The statement returns the following XML result set:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee EmpID="4">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName xsi:nil="true" />

      <LastName>Walters</LastName>

    </EmployeeName>

  </Employee>

  <Employee EmpID="168">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName>T</MiddleName>

      <LastName>Caron</LastName>

    </EmployeeName>

  </Employee>

</Employees>

Notice that each <Employee>element now includes an <EmployeeName> element, and each of those elements includes the individual parts of the name.

Suppose that you now want to add an email address to your result set. You can simply add the column to the SELECT list after the other columns, as shown in the following example:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName AS "EmployeeName/FirstName",

   c.MiddleName AS "EmployeeName/MiddleName",

   c.LastName AS "EmployeeName/LastName",

   c.EmailAddress

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Because the column name is EmailAddress and no alias has been defined on that column, your XML results will now include the <EmailAddress> element as a child element to <Employee>,right after <EmployeeName>:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee EmpID="4">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName xsi:nil="true" />

      <LastName>Walters</LastName>

    </EmployeeName>

    <EmailAddress>rob0@adventure-works.com</EmailAddress>

  </Employee>

  <Employee EmpID="168">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName>T</MiddleName>

      <LastName>Caron</LastName>

    </EmployeeName>

    <EmailAddress>rob1@adventure-works.com</EmailAddress>

  </Employee>

</Employees>

You must be careful on how you order your columns in the SELECT list. For example, in the following SELECT statement, I added the EmailAddress column after MiddleName, but before LastName:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName AS "EmployeeName/FirstName",

   c.MiddleName AS "EmployeeName/MiddleName",

   c.EmailAddress,

   c.LastName AS "EmployeeName/LastName"

FROM HumanResources.Employee AS e

   INNER JOIN Person.Contact AS c

   ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob'

FOR XML PATH ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Because I do not list the parts of the employee names consecutively, they are separated in the XML results:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <Employee EmpID="4">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName xsi:nil="true" />

    </EmployeeName>

    <EmailAddress>rob0@adventure-works.com</EmailAddress>

    <EmployeeName>

      <LastName>Walters</LastName>

    </EmployeeName>

  </Employee>

  <Employee EmpID="168">

    <EmployeeName>

      <FirstName>Rob</FirstName>

      <MiddleName>T</MiddleName>

    </EmployeeName>

    <EmailAddress>rob1@adventure-works.com</EmailAddress>

    <EmployeeName>

      <LastName>Caron</LastName>

    </EmployeeName>

  </Employee>

</Employees>

As the XML shows, there are now two instances of the <EmployeeName> child element in each <Employee> element. The way to address this issue is to make certain you list the columns in your SELECT list in the order you want the XML rendered.

In an earlier example, I demonstrated how to include an XML column in your query. You can also include an XML column when using the PATH mode. The XML data returned by the column is incorporated into the XML that is returned by the query. For instance, the following SELECT statement adds education data to the result set:

SELECT e.EmployeeID AS "@EmpID",

   c.FirstName AS "EmployeeName/FirstName",

   c.MiddleName AS "EmployeeName/MiddleName",

   c.LastName AS "EmployeeName/LastName",

   c.EmailAddress,

   jc.Resume.query('declare namespace ns=

      "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

      /ns:Resume/ns:Education')

FROM HumanResources.Employee e INNER JOIN Person.Contact c

   ON c.ContactID = e.ContactID

   INNER JOIN HumanResources.JobCandidate jc

   ON e.EmployeeID = jc.EmployeeID

WHERE e.EmployeeID = 268

FOR XML PATH ('Employee');

The <Education> element and child elements are now included the XML result set:

<Employee EmpID="268">

  <EmployeeName>

    <FirstName>Stephen</FirstName>

    <MiddleName>Y</MiddleName>

    <LastName>Jiang</LastName>

  </EmployeeName>

  <EmailAddress>stephen0@adventure-works.com</EmailAddress>

  <ns:Education xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">

    <ns:Edu.Level>Bachelor</ns:Edu.Level>

    <ns:Edu.StartDate>1986-09-15Z</ns:Edu.StartDate>

    <ns:Edu.EndDate>1990-05-20Z</ns:Edu.EndDate>

    <ns:Edu.Degree>Bachelor of Arts and Science</ns:Edu.Degree>

    <ns:Edu.Major>Business</ns:Edu.Major>

    <ns:Edu.Minor />

    <ns:Edu.GPA>3.3</ns:Edu.GPA>

    <ns:Edu.GPAScale>4</ns:Edu.GPAScale>

    <ns:Edu.School>Louisiana Business College of New Orleans</ns:Edu.School>

    <ns:Edu.Location>

      <ns:Location>

        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>

        <ns:Loc.State>LA</ns:Loc.State>

        <ns:Loc.City>New Orleans</ns:Loc.City>

      </ns:Location>

    </ns:Edu.Location>

  </ns:Education>

</Employee>

As these preceding examples demonstrate, the PATH mode provides a relatively easy way to define elements and attributes in your XML result set. However, the PATH mode, like the other FOR XML modes, supports additional options. For that reason, be sure to check out SQL Server Books Online for more information about each mode and about the FOR XML clause in general. Despite how basic the clause itself might seem, it provides numerous options for returning exactly the type of XML data you need.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 393 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Using the FOR XML Clause to Return Query Results as XML
Posted by: Setaceo (not signed in)
Posted on: Friday, May 29, 2009 at 8:46 AM
Message: Its very useful. Thanx

Subject: Using the FOR XML Clause to Return Query Results as XML
Posted by: Michael Rys (MSFT) (not signed in)
Posted on: Monday, June 01, 2009 at 3:50 PM
Message: Thanks Robert for the nice intro to FOR XML. Let me point out that FOR XML queries can be nested in subqueries.

You may also want to read the whitepaper <a href="http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx">What's New in FOR XML in Microsoft SQL Server 2005 (http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx)</a>.

Cheers
Michael

Subject: Very Helpfull
Posted by: NoumanUllah Siddiqui (not signed in)
Posted on: Monday, June 01, 2009 at 4:27 PM
Message: Great article thank you for sharing.

Subject: Using the FOR XML Clause to Return Query Results as XML
Posted by: surender kumar (not signed in)
Posted on: Monday, June 01, 2009 at 11:33 PM
Message: Very helpful to know about XML through your article. Good Robert.

Subject: Using the FOR XML Clause to Return Query Results as XML
Posted by: Jason Rogers (not signed in)
Posted on: Tuesday, June 02, 2009 at 6:01 AM
Message: Great article, thank you. One I will definitely be bookmarking

Subject: Very useful
Posted by: Raymond Amegadjin (not signed in)
Posted on: Wednesday, June 03, 2009 at 4:12 AM
Message: Very useful article, i had some problem explaining the use of the 'tag' and 'parent' in the FOR XML EXPLICIT.
Thanks , wish we had an article on xquery

Subject: nice post
Posted by: Sriram (view profile)
Posted on: Wednesday, June 03, 2009 at 6:46 AM
Message: Thanks that was really simple and was a gr8 help...

Subject: Oh Yeah
Posted by: Philip Kelley (view profile)
Posted on: Wednesday, June 03, 2009 at 11:52 AM
Message: Clear, legible, insanely useful, and added to my SQL Server bookmarks.

Subject: Easy to Learn XML with SQL
Posted by: Jiban Kumar Jena (not signed in)
Posted on: Friday, June 05, 2009 at 6:14 AM
Message: Great Robert,

This will help a lot to the going to be advanced learner in SQL DB. Hope we will get this like article in near future.

Subject: errors in results if parallel plan used
Posted by: Joe Moyle (not signed in)
Posted on: Sunday, June 07, 2009 at 10:45 AM
Message: I've encountered errors in the resulting XML when the engine chooses a parallel plan. I've seen it split what should have been one set of elements in two and also give duplicate data. I've gotten around this by adding the hint OPTION (MAXDOP 1)
right after the FOR XML portion of my select statements.

Subject: excellent
Posted by: Anonymous (not signed in)
Posted on: Monday, June 08, 2009 at 2:14 AM
Message: great article

Subject: Excellent
Posted by: Kader (not signed in)
Posted on: Tuesday, June 09, 2009 at 9:01 PM
Message: Excellent article very clear and helpfull

Subject: super
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 11, 2009 at 6:21 AM
Message: simply excellent

Subject: Good Job
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 11, 2009 at 6:46 AM
Message: I've used FOR XML on a few occasions and just muddle my way through until I get the results I want. This article is definitely Bookmarkable!

Subject: ?????
Posted by: Kish (not signed in)
Posted on: Thursday, June 11, 2009 at 8:15 AM
Message: SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName

FROM HumanResources.Employee e INNER JOIN Person.Contact c

ON c.ContactID = e.ContactID

WHERE c.FirstName = 'Rob' DID'nt worked

FOR XML RAW ('Employee'), ROOT;

Subject: the BOMB!
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 11, 2009 at 8:30 AM
Message: Books online definatley does not have this detail. Wish it did when i needed it

Subject: Brilliant Article
Posted by: Kirky (not signed in)
Posted on: Thursday, June 11, 2009 at 7:23 PM
Message: Love this article. I can instantly create XML Stored Procs for so many things.

Right now i'm generating RSS feeds galore but the one part of the puzzle missing is how to output the results from a Stored Proc directly to a text file output?

I've heard you can do this via BCP or CLR. I don't want to use BCP so is there an easy way someone can suggest?

Subject: Very nice
Posted by: Ravi Kumar (view profile)
Posted on: Friday, June 12, 2009 at 4:25 AM
Message: Very good article

Subject: Really power-packed
Posted by: KuyreST (view profile)
Posted on: Thursday, June 18, 2009 at 5:35 AM
Message: Thanks Robert!!! for this power-packed article. In just few minutes of reading, it made me understand fully the XML clause in SQL. We developers need such nicely written articles to cut-down development time.

Thanks again.

Subject: amazing article
Posted by: idris (view profile)
Posted on: Thursday, October 01, 2009 at 1:50 AM
Message: Its hase been very informative but i was wondering if we can avoid special charecters when the xml is generated.

Thanks

Subject: Excellent article
Posted by: rhonalds (view profile)
Posted on: Sunday, May 09, 2010 at 7:17 PM
Message: Very informative.

Subject: XLM trouble
Posted by: Prince K (view profile)
Posted on: Tuesday, November 08, 2011 at 10:51 AM
Message: I'm having issues trying to include the XML header to look like the following

<mediaFeed xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd">
</mediaFeed>

Everytime i run the query i get following:

<mediaFeed>xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd"</mediaFeed>

Below is the query im running:

SELECT(
SELECT 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd"',
(SELECT
(SELECT ICPN AS 'EAN'
FROM tbConfiguration cx1
WHERE cx1.ConfigurationId = c.ConfigurationId
FOR XML Path('itemIdentifier'), TYPE),
(SELECT ConfigurationTitle AS Title
FROM tbConfiguration cx2
WHERE cx2.ConfigurationId = c.ConfigurationId
FOR XML Path('OriginalLanguage'), ROOT('title'), Type),

ISNULL(afm.AmazonFormatName, cf.FormatName) AS productForm,

(SELECT ISNULL(arm.AmazonRoleName, cr3.RoleName) AS Role,
cn3.Name AS Name
FROM tbConfiguration cx3
JOIN tbConfigurationContributor cc3 on cc3.ConfigurationId = c.ConfigurationId
JOIN tbContributorRole cr3 on cr3.RoleId = cc3.RoleId
JOIN tbContributorName cn3 on cn3.ContributorId = cc3.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm ON arm.RoleId = cr3.RoleId
WHERE cx3.ConfigurationId = c.ConfigurationId
FOR XML Path('contributor'), ROOT('contributors'), Type),

-- Music
(SELECT
(SELECT TOP 1 ISNULL(agm.AmazonGenreName, GenreDescription) AS Value
FROM tbConfiguration cx4
JOIN tbConfigurationGenre cg4 on cg4.ConfigurationId = cx4.ConfigurationId
JOIN tbGenre g4 on g4.GenreId = cg4.GenreId
LEFT JOIN tbAmazonGenreMapping agm ON g4.GenreId = agm.GenreId
WHERE cx4.ConfigurationId = c.ConfigurationId
FOR XML Path('genre'), Type),
(CASE ParentalAdvisoryIndicatorLyrics
WHEN 'N' THEN
'False'
WHEN 'Y' THEN
'True'
END) AS isExplicitLyrics,
(SELECT VersionTitle AS 'value',
(CASE CompilationIndicator WHEN 'Y' THEN 'Complitation' ELSE NULL END) AS 'value'
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
AND (CompilationIndicator = 'Y'
OR versiontitle IS NOT NULL)
FOR XML Path('contentType'), type)
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
FOR XML Path('music'), Type),
-- Owner Info
(SELECT 'EMI' AS corporateGroup,
RTRIM(phi1.Label) AS Company
FROM tbSAPPhysicalImport phi1 (NOLOCK)
WHERE phi1.ICPN = c.ICPN
FOR XML Path('ownerInfo'), Type),

(CASE RTRIM(LTRIM(DeleteDate)) WHEN '00000000' THEN
CASE WHEN CONVERT(DATETIME, ReleaseDate, 101) > GETDATE() THEN 'Not yet published'
ELSE 'In print'
END
ELSE 'Out of print' END) AS lifeCycle,
-- (CASE phi.LifeCycle WHEN 1 THEN 'Available' ELSE 'Not Available' END) AS lifeCycle,
-- Run Time
(CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime / 3600)) = 1 THEN
'0'+ CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + ':'
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60)) = 1 THEN
'0' + CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + ':'
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))) = 1 THEN
'0' + CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))
ELSE CONVERT(VARCHAR(5), (c.TotalMusicTime % 60)) END) AS runtime,
(LEFT(phi.ReleaseDate,4) + '-' + SUBSTRING(phi.ReleaseDate,5,2) + '-' + SUBSTRING(phi.ReleaseDate,7,2)) AS releaseDate,
--Original Release Date
(SELECT CONVERT(VARCHAR(10), MIN(LocalReleaseDate),120)
FROM tbProductExploitation pex
WHERE pex.ConfigurationId = c.ConfigurationId) AS originalReleaseDate,
-- '' AS sellable,
(SELECT phi1.DealerPrice AS Price,
phi1.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi1
JOIN tbConfiguration cx7 on cx7.ICPN = phi1.ICPN
WHERE cx7.ConfigurationId = c.ConfigurationId
FOR XML Path('priceWithCurrency'), ROOT('listPriceWithoutTax'), type),
(SELECT phi2.DealerPrice AS price,
phi2.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi2
JOIN tbConfiguration cx8 on cx8.ICPN = phi2.ICPN
WHERE cx8.ConfigurationId = c.ConfigurationId
FOR XML Path('priceWithCurrency'), ROOT('listPriceWithTax'), type),
(CASE c.BoxedSetIndicator
WHEN 1 THEN (SELECT COUNT(*) FROM tbBoxedSetComponent WHERE boxedSetConfigId = c.ConfigurationId)
ELSE (SELECT COUNT(*) FROM tbComponent WHERE ConfigurationId = c.ConfigurationId)
END) AS numberOfItems,
-- Tracks
(SELECT r6.ISRC AS isrcCode,
r6.repertoireTitle AS title,
(SELECT * FROM
(SELECT ISNULL(arm1.AmazonRoleName, cr7.RoleName) AS role,
cn7.Name AS name
FROM tbRecording r7
JOIN tbRecordingContributor rc7 on rc7.RecordingId = r7.RecordingId
AND RoleId IN (1, 2)
JOIN tbContributorRole cr7 on cr7.RoleId = rc7.RoleId
JOIN tbContributorName cn7 on cn7.ContributorId = rc7.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm1 ON arm1.RoleId = cr7.RoleId
WHERE r7.recordingID = r6.recordingID
UNION
SELECT ISNULL(arm2.AmazonRoleName, cr8.RoleName) AS role,
cn8.Name AS Name
FROM tbRecording r8
JOIN tbRecordingComposition rc8 on rc8.RecordingId = r8.RecordingId
JOIN tbCompositionContributor cc8 on cc8.CompositionId = rc8.CompositionId
JOIN tbContributorRole cr8 on cr8.RoleId = cc8.RoleId
JOIN tbContributorName cn8 on cn8.ContributorId = cc8.ContributorId
LEFT JOIN tbAmazonRoleMapping arm2 ON arm2.RoleId = cr8.RoleId
WHERE r8.RecordingId = r6.RecordingId) a
FOR XML Path('contributor'), ROOT('contributors'), Type),
t6.ComponentNumber AS discNumber,
t6.SideNumber AS sideNumber,
t6.LogicalTrackNumber AS trackNumber,
(CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming / 3600)) = 1 THEN
'0'+ CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + ':'
ELSE CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60)) = 1 THEN
'0' + CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + ':'
ELSE CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (t6.TrackTiming % 60))) = 1 THEN
'0' + CONVERT(VARCHAR(5), (t6.TrackTiming % 60))
ELSE CONVERT(VARCHAR(5), (t6.TrackTiming % 60)) END) AS trackDuration
FROM tbConfiguration cx6
JOIN tbtrack t6 on t6.ConfigurationId = cx6.ConfigurationId
JOIN tbrecording r6 on r6.recordingID = t6.recordingiD
WHERE cx6.ConfigurationId = c.ConfigurationId
FOR XML PATH('track'), ROOT('tracks'), Type)

FROM tbConfiguration c
JOIN tbSAPPhysicalImport (NOLOCK) Phi ON phi.ICPN = c.ICPN
JOIN tbconfigurationFormat cf ON cf.FormatId = c.FormatId
LEFT JOIN tbAmazonFormatMapping afm ON afm.FormatId = cf.FormatId
FOR XML PATH('mediaItem'), Type)
FOR XML PATH('mediaFeed'), Type
</mediaFeed>

Can you any one help me, many thanks in advance

Subject: XLM trouble
Posted by: Prince K (view profile)
Posted on: Tuesday, November 08, 2011 at 10:56 AM
Message: I'm having issues trying to include the XML header to look like the following

<mediaFeed xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd">
</mediaFeed>

Everytime i run the query i get following:

<mediaFeed>xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd"</mediaFeed>

Below is the query im running:

SELECT(
SELECT 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="AMF-XML-Schema.xsd"',
(SELECT
(SELECT ICPN AS 'EAN'
FROM tbConfiguration cx1
WHERE cx1.ConfigurationId = c.ConfigurationId
FOR XML Path('itemIdentifier'), TYPE),
(SELECT ConfigurationTitle AS Title
FROM tbConfiguration cx2
WHERE cx2.ConfigurationId = c.ConfigurationId
FOR XML Path('OriginalLanguage'), ROOT('title'), Type),

ISNULL(afm.AmazonFormatName, cf.FormatName) AS productForm,

(SELECT ISNULL(arm.AmazonRoleName, cr3.RoleName) AS Role,
cn3.Name AS Name
FROM tbConfiguration cx3
JOIN tbConfigurationContributor cc3 on cc3.ConfigurationId = c.ConfigurationId
JOIN tbContributorRole cr3 on cr3.RoleId = cc3.RoleId
JOIN tbContributorName cn3 on cn3.ContributorId = cc3.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm ON arm.RoleId = cr3.RoleId
WHERE cx3.ConfigurationId = c.ConfigurationId
FOR XML Path('contributor'), ROOT('contributors'), Type),

-- Music
(SELECT
(SELECT TOP 1 ISNULL(agm.AmazonGenreName, GenreDescription) AS Value
FROM tbConfiguration cx4
JOIN tbConfigurationGenre cg4 on cg4.ConfigurationId = cx4.ConfigurationId
JOIN tbGenre g4 on g4.GenreId = cg4.GenreId
LEFT JOIN tbAmazonGenreMapping agm ON g4.GenreId = agm.GenreId
WHERE cx4.ConfigurationId = c.ConfigurationId
FOR XML Path('genre'), Type),
(CASE ParentalAdvisoryIndicatorLyrics
WHEN 'N' THEN
'False'
WHEN 'Y' THEN
'True'
END) AS isExplicitLyrics,
(SELECT VersionTitle AS 'value',
(CASE CompilationIndicator WHEN 'Y' THEN 'Complitation' ELSE NULL END) AS 'value'
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
AND (CompilationIndicator = 'Y'
OR versiontitle IS NOT NULL)
FOR XML Path('contentType'), type)
FROM tbConfiguration cx5
WHERE cx5.ConfigurationId = c.ConfigurationId
FOR XML Path('music'), Type),
-- Owner Info
(SELECT 'EMI' AS corporateGroup,
RTRIM(phi1.Label) AS Company
FROM tbSAPPhysicalImport phi1 (NOLOCK)
WHERE phi1.ICPN = c.ICPN
FOR XML Path('ownerInfo'), Type),

(CASE RTRIM(LTRIM(DeleteDate)) WHEN '00000000' THEN
CASE WHEN CONVERT(DATETIME, ReleaseDate, 101) > GETDATE() THEN 'Not yet published'
ELSE 'In print'
END
ELSE 'Out of print' END) AS lifeCycle,
-- (CASE phi.LifeCycle WHEN 1 THEN 'Available' ELSE 'Not Available' END) AS lifeCycle,
-- Run Time
(CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime / 3600)) = 1 THEN
'0'+ CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + ':'
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime / 3600) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60)) = 1 THEN
'0' + CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + ':'
ELSE CONVERT(VARCHAR(5), c.TotalMusicTime % 3600 / 60) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))) = 1 THEN
'0' + CONVERT(VARCHAR(5), (c.TotalMusicTime % 60))
ELSE CONVERT(VARCHAR(5), (c.TotalMusicTime % 60)) END) AS runtime,
(LEFT(phi.ReleaseDate,4) + '-' + SUBSTRING(phi.ReleaseDate,5,2) + '-' + SUBSTRING(phi.ReleaseDate,7,2)) AS releaseDate,
--Original Release Date
(SELECT CONVERT(VARCHAR(10), MIN(LocalReleaseDate),120)
FROM tbProductExploitation pex
WHERE pex.ConfigurationId = c.ConfigurationId) AS originalReleaseDate,
-- '' AS sellable,
(SELECT phi1.DealerPrice AS Price,
phi1.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi1
JOIN tbConfiguration cx7 on cx7.ICPN = phi1.ICPN
WHERE cx7.ConfigurationId = c.ConfigurationId
FOR XML Path('priceWithCurrency'), ROOT('listPriceWithoutTax'), type),
(SELECT phi2.DealerPrice AS price,
phi2.Currency AS currencyUnit
FROM tbSAPPhysicalImport (NOLOCK) phi2
JOIN tbConfiguration cx8 on cx8.ICPN = phi2.ICPN
WHERE cx8.ConfigurationId = c.ConfigurationId
FOR XML Path('priceWithCurrency'), ROOT('listPriceWithTax'), type),
(CASE c.BoxedSetIndicator
WHEN 1 THEN (SELECT COUNT(*) FROM tbBoxedSetComponent WHERE boxedSetConfigId = c.ConfigurationId)
ELSE (SELECT COUNT(*) FROM tbComponent WHERE ConfigurationId = c.ConfigurationId)
END) AS numberOfItems,
-- Tracks
(SELECT r6.ISRC AS isrcCode,
r6.repertoireTitle AS title,
(SELECT * FROM
(SELECT ISNULL(arm1.AmazonRoleName, cr7.RoleName) AS role,
cn7.Name AS name
FROM tbRecording r7
JOIN tbRecordingContributor rc7 on rc7.RecordingId = r7.RecordingId
AND RoleId IN (1, 2)
JOIN tbContributorRole cr7 on cr7.RoleId = rc7.RoleId
JOIN tbContributorName cn7 on cn7.ContributorId = rc7.ContributorId
AND IsDefaultName = 1
LEFT JOIN tbAmazonRoleMapping arm1 ON arm1.RoleId = cr7.RoleId
WHERE r7.recordingID = r6.recordingID
UNION
SELECT ISNULL(arm2.AmazonRoleName, cr8.RoleName) AS role,
cn8.Name AS Name
FROM tbRecording r8
JOIN tbRecordingComposition rc8 on rc8.RecordingId = r8.RecordingId
JOIN tbCompositionContributor cc8 on cc8.CompositionId = rc8.CompositionId
JOIN tbContributorRole cr8 on cr8.RoleId = cc8.RoleId
JOIN tbContributorName cn8 on cn8.ContributorId = cc8.ContributorId
LEFT JOIN tbAmazonRoleMapping arm2 ON arm2.RoleId = cr8.RoleId
WHERE r8.RecordingId = r6.RecordingId) a
FOR XML Path('contributor'), ROOT('contributors'), Type),
t6.ComponentNumber AS discNumber,
t6.SideNumber AS sideNumber,
t6.LogicalTrackNumber AS trackNumber,
(CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming / 3600)) = 1 THEN
'0'+ CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + ':'
ELSE CONVERT(VARCHAR(5), t6.TrackTiming / 3600) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60)) = 1 THEN
'0' + CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + ':'
ELSE CONVERT(VARCHAR(5), t6.TrackTiming % 3600 / 60) + ':' END +
CASE
WHEN LEN(CONVERT(VARCHAR(5), (t6.TrackTiming % 60))) = 1 THEN
'0' + CONVERT(VARCHAR(5), (t6.TrackTiming % 60))
ELSE CONVERT(VARCHAR(5), (t6.TrackTiming % 60)) END) AS trackDuration
FROM tbConfiguration cx6
JOIN tbtrack t6 on t6.ConfigurationId = cx6.ConfigurationId
JOIN tbrecording r6 on r6.recordingID = t6.recordingiD
WHERE cx6.ConfigurationId = c.ConfigurationId
FOR XML PATH('track'), ROOT('tracks'), Type)

FROM tbConfiguration c
JOIN tbSAPPhysicalImport (NOLOCK) Phi ON phi.ICPN = c.ICPN
JOIN tbconfigurationFormat cf ON cf.FormatId = c.FormatId
LEFT JOIN tbAmazonFormatMapping afm ON afm.FormatId = cf.FormatId
FOR XML PATH('mediaItem'), Type)
FOR XML PATH('mediaFeed'), Type
</mediaFeed>

Can you any one help me, many thanks in advance

Subject: XML SCHEMA
Posted by: MidoMis (view profile)
Posted on: Monday, May 28, 2012 at 2:42 AM
Message: im trying to change the Schema location to another schema but i donno how ?

Subject: SQL to XML made simple - new open source tool available
Posted by: xmlorb (view profile)
Posted on: Thursday, October 11, 2012 at 7:45 PM
Message: While all this article is very good - there is a much simpler way to do all this! How about visual drag and drop directly from your SQL to the XML - completely code-free and then just run to get your output?

Check out the demonstration video here:
http://www.youtube.com/user/TheCAMeditor

and the open source tool itself here:
http://www.cameditor.org

Enjoy

Subject: Related tables into XML
Posted by: Dhinakaran (view profile)
Posted on: Monday, November 26, 2012 at 9:56 AM
Message: Hi sir. This was very useful article.
I have a scenario where I am having 2 tables say 1st table have column customername and 2nd table have column address.

One customer can have many address.
So I need these data into single XML format where for example customername A should be Main node and all his address should be formed as Child node or Sub Node.

Can you please tell me how to achieve this.

Subject: Very very useful
Posted by: rajeshkannans (view profile)
Posted on: Tuesday, April 09, 2013 at 4:28 AM
Message: I have been searching "for-xml in sql server" for a long time. Now I have found out.
This has all the information to become expert in xml manipulation in sql server queries.

Much thanks to the author!

--
Rajesh,
Software Engineer.

Subject: Cannot get XML output to preserve formatting
Posted by: tstoneami (view profile)
Posted on: Thursday, November 21, 2013 at 2:37 PM
Message: I have a query producing my XML output just fine - in SSMS. The file it outputs to (when using sqlcmd with a sproc) doesn't preserve the formatting.

Its all there, just the way I need it - but its a stream. Have tried :XML ON - but SSMS query window doesn't like it. Can't figure out where to place it in sqlcmd either.

I simply can't get the xml output to preserve the formatting displayed in SSMS. PLEASE HELP! I have really tried to get this working - but can't.

Sorry if this is the wrong forum to ask for help in - but this article is very concise and it seemed a good place to start.

Regards,

troy

Subject: Fantastic Article
Posted by: DiggaTheWolf (view profile)
Posted on: Friday, March 21, 2014 at 1:05 AM
Message: Hi Robert

I wish all articles were written like this, simple, clear, concise and with excellent examples of code and outputs. An excellent starting point for SQL and XML.

Many thanks for taking the time putting this together. Emergency rescues loss is definitely our gain.

Kind regards,

Lee

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.