Hello friends, We have already explain regarding IIF() in SQL Server,Right Join in SQL Server,left join in SQL, Self Join in SQL Server , RANK(),  DENSE_RANK(), ROW_NUMBER() and  Aggregate functions in sql server. Today I am going to explain regarding Create XML in SQL Server, however this is my first step toward writing post.  I will also explain “How to create XML by using EXPLICIT Mode with FOR XML in SQL Server”. I will explain EXPLICIT Mode with FOR XML in SQL Server in detail with examples.

There are four modes supported by FOR XML clause:

1. RAW

2. AUTO

3. EXPLICIT

4. PATH

In this article I will explain regarding EXPLICIT mode with for xml clause.

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.

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.

Syntax as per MSDN:

<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.

Note:

The following applies to the WITH XMLNAMESPACES clause:
It is supported only on the RAW, AUTO, and PATH modes of the FOR XML queries. The EXPLICIT mode is not supported.
It only affects the namespace prefixes of FOR XML queries and the xml data type methods, but not the XML parser. For example, the following query returns an error, because the XML document has no namespace declaration for the myNS prefix.
The FOR XML directives, XMLSCHEMA and XMLDATA cannot be used when a WITH XMLNAMESPACES clause is being used.

Creating a table variable and inserting some records into it.

DECLARE @tmpAgency AS TABLE (
	agency_id INT
	,agency_name VARCHAR(50)
	,agency_code VARCHAR(50)
	)

INSERT INTO @tmpAgency (
	agency_id
	,agency_name
	,agency_code
	)
SELECT 1 ,'A and k Fowler Insurance Agency' ,'AG1011'           UNION ALL
SELECT 2 ,'Doherty Ins Agency Inc.','AGT10001'                  UNION ALL
SELECT 3 ,'Doherty-White Insurance Agency,Inc.','AGT10012'      UNION ALL
SELECT 4 ,'John F. Dolan AGT10034' ,'AGT10034'                  UNION ALL
SELECT 5 ,'P.S. Dolan InsuranceUSE AGT 59771' ,'AGT10045'

SELECT * FROM @tmpAgency AgencyInfo

OUTPUT:

SELECT TOP 1 1 AS Tag
	,NULL AS Parent
	,NULL AS [Retailer!1!]
	,NULL AS [AgencyInfo!2!AgencyID]
	,NULL AS [AgencyInfo!2!AgencyName!ELEMENT]
	,NULL AS [AgencyInfo!2!AgencyCode!ELEMENT]
FROM @tmpAgency t

UNION ALL

SELECT 2 AS Tag
	,1 AS Parent
	,NULL
	,t.agency_id
	,t.agency_name
	,t.agency_code
FROM @tmpAgency t
ORDER BY [Retailer!1!]
	,[AgencyInfo!2!AgencyName!ELEMENT]
FOR XML EXPLICIT

OUTPUT:

<Retailer>
  <AgencyInfo AgencyID="A and k Fowler Insurance Agency">
    <AgencyName>1</AgencyName>
    <AgencyCode>AG1011</AgencyCode>
  </AgencyInfo>
  <AgencyInfo AgencyID="Doherty Ins Agency Inc.">
    <AgencyName>2</AgencyName>
    <AgencyCode>AGT10001</AgencyCode>
  </AgencyInfo>
  <AgencyInfo AgencyID="Doherty-White Insurance Agency,Inc.">
    <AgencyName>3</AgencyName>
    <AgencyCode>AGT10012</AgencyCode>
  </AgencyInfo>
  <AgencyInfo AgencyID="John F. Dolan AGT10034">
    <AgencyName>4</AgencyName>
    <AgencyCode>AGT10034</AgencyCode>
  </AgencyInfo>
  <AgencyInfo AgencyID="P.S. Dolan InsuranceUSE AGT 59771">
    <AgencyName>5</AgencyName>
    <AgencyCode>AGT10045</AgencyCode>
  </AgencyInfo>
</Retailer>

In the above example, there is only one Root element <Retailer> thats why I have written

SELECT TOP 1 .The query is the declaration of Element structure of XML.

[Retailer!1!] descibes that <Retailer> tag has on any attribute and this is the first parent element of XML hierarchy. [AgencyInfo!2!AgencyID] descibes that <AgencyInfo> tag has on one attribute AgencyId and this is the second parent element. [AgencyInfo!2!AgencyName!ELEMENT]  and [AgencyInfo!2!AgencyCode!ELEMENT] descibes that <AgencyInfo> tag has two ELEMENTS AgencyName , AgencyCode one by one and both are child elements of <AgencyInfo> tag.

However, in the second query ,  SELECT 2 AS Tag shows that AgencyInfo is second Parent Element and the values of attributes and child elements have been given by defining the column names.

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.

Thanks for reading regarding EXPLICIT Mode with FOR XML. Stay thirsty with technothirsty. Don’t forgot to comment.