AUTO Mode with FOR XML

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 AUTO Mode with FOR XML in SQL Server”. I will explain AUTO Mode with FOR XML in detail with examples.

There are four modes supported by FOR XML clause:

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

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 which is based on how the SELECT statement is defined.

The best way to understand how this works is to look at an example. First of all I have to create Table Variables.

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:

Another Table Variable

DECLARE @tmpAgent AS TABLE (
agent_id INT
,agent_name VARCHAR(50)
,agency_id INT
)

INSERT INTO @tmpAgent (
		agent_id
		,agent_name
		,agency_id
		)
SELECT 1,'Patrick McCalls',1	UNION ALL
SELECT 2,'Crystal Bomer',1		UNION ALL
SELECT 3,'Andre Porter',1		UNION ALL
SELECT 4,'Pam Rowley',2		UNION ALL
SELECT 5,'Catherine Brunelle',2	UNION ALL
SELECT 6,'Rita Belmonte',3		UNION ALL
SELECT 7,'Cliff W Adams',3		UNION ALL
SELECT 8,'Diane LeBlanc',4		UNION ALL
SELECT 9,'Sheila M. Doherty',4	UNION ALL
SELECT 10,'John F Dolan',4		UNION ALL
SELECT 11,'Ed Juliano',4		UNION ALL
SELECT 12,'Harvey Doren',5		UNION ALL
SELECT 13,'Susan Clancy',5		

SELECT * FROM @tmpAgent

OUTPUT:

The following example shows, how to create simple XML by using AUTO mode.

SELECT *
FROM @tmpAgency AgencyInfo
JOIN @tmpAgent AgentInfo ON AgencyInfo.agency_id = AgentInfo.agency_id
FOR XML AUTO

OUTPUT:

<AgencyInfo agency_id="1" agency_name="A and k Fowler Insurance Agency" agency_code="AG1011">
  <AgentInfo agent_id="1" agent_name="Patrick McCalls" agency_id="1" />
  <AgentInfo agent_id="2" agent_name="Crystal Bomer" agency_id="1" />
  <AgentInfo agent_id="3" agent_name="Andre Porter" agency_id="1" />
</AgencyInfo>
<AgencyInfo agency_id="2" agency_name="Doherty Ins Agency Inc." agency_code="AGT10001">
  <AgentInfo agent_id="4" agent_name="Pam Rowley" agency_id="2" />
  <AgentInfo agent_id="5" agent_name="Catherine Brunelle" agency_id="2" />
</AgencyInfo>
<AgencyInfo agency_id="3" agency_name="Doherty-White Insurance Agency,Inc." agency_code="AGT10012">
  <AgentInfo agent_id="6" agent_name="Rita Belmonte" agency_id="3" />
  <AgentInfo agent_id="7" agent_name="Cliff W Adams" agency_id="3" />
</AgencyInfo>
<AgencyInfo agency_id="4" agency_name="John F. Dolan AGT10034" agency_code="AGT10034">
  <AgentInfo agent_id="8" agent_name="Diane LeBlanc" agency_id="4" />
  <AgentInfo agent_id="9" agent_name="Sheila M. Doherty" agency_id="4" />
  <AgentInfo agent_id="10" agent_name="John F Dolan" agency_id="4" />
  <AgentInfo agent_id="11" agent_name="Ed Juliano" agency_id="4" />
</AgencyInfo>
<AgencyInfo agency_id="5" agency_name="P.S. Dolan InsuranceUSE AGT 59771" agency_code="AGT10045">
  <AgentInfo agent_id="12" agent_name="Harvey Doren" agency_id="5" />
  <AgentInfo agent_id="13" agent_name="Susan Clancy" agency_id="5" />
</AgencyInfo>

In this example, AgencyInfo is a Parent tag and AgentInfo is a Child tag. This become because of the relationship of AgencyInfo and AgentInfo table in the query. Both tables are joined with each other.

Here is the example of using Root clause with Auto mode.

SELECT *
FROM @tmpAgency AgencyInfo
JOIN @tmpAgent AgentInfo ON AgencyInfo.agency_id = AgentInfo.agency_id
FOR XML AUTO
	,Root('Agencies')

<Agencies>
  <AgencyInfo agency_id="1" agency_name="A and k Fowler Insurance Agency" agency_code="AG1011">
    <AgentInfo agent_id="1" agent_name="Patrick McCalls" agency_id="1" />
    <AgentInfo agent_id="2" agent_name="Crystal Bomer" agency_id="1" />
    <AgentInfo agent_id="3" agent_name="Andre Porter" agency_id="1" />
  </AgencyInfo>
  <AgencyInfo agency_id="2" agency_name="Doherty Ins Agency Inc." agency_code="AGT10001">
    <AgentInfo agent_id="4" agent_name="Pam Rowley" agency_id="2" />
    <AgentInfo agent_id="5" agent_name="Catherine Brunelle" agency_id="2" />
  </AgencyInfo>
  <AgencyInfo agency_id="3" agency_name="Doherty-White Insurance Agency,Inc." agency_code="AGT10012">
    <AgentInfo agent_id="6" agent_name="Rita Belmonte" agency_id="3" />
    <AgentInfo agent_id="7" agent_name="Cliff W Adams" agency_id="3" />
  </AgencyInfo>
  <AgencyInfo agency_id="4" agency_name="John F. Dolan AGT10034" agency_code="AGT10034">
    <AgentInfo agent_id="8" agent_name="Diane LeBlanc" agency_id="4" />
    <AgentInfo agent_id="9" agent_name="Sheila M. Doherty" agency_id="4" />
    <AgentInfo agent_id="10" agent_name="John F Dolan" agency_id="4" />
    <AgentInfo agent_id="11" agent_name="Ed Juliano" agency_id="4" />
  </AgencyInfo>
  <AgencyInfo agency_id="5" agency_name="P.S. Dolan InsuranceUSE AGT 59771" agency_code="AGT10045">
    <AgentInfo agent_id="12" agent_name="Harvey Doren" agency_id="5" />
    <AgentInfo agent_id="13" agent_name="Susan Clancy" agency_id="5" />
  </AgencyInfo>
</Agencies>

You can also define each columns as separate elements by using ELEMENTS clause .

SELECT *
FROM @tmpAgency AgencyInfo
JOIN @tmpAgent AgentInfo ON AgencyInfo.agency_id = AgentInfo.agency_id
FOR XML AUTO
	,Root('Agencies')
	,ELEMENTS

OUTPUT:

<Agencies>
  <AgencyInfo>
    <agency_id>1</agency_id>
    <agency_name>A and k Fowler Insurance Agency</agency_name>
    <agency_code>AG1011</agency_code>
    <AgentInfo>
      <agent_id>1</agent_id>
      <agent_name>Patrick McCalls</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>2</agent_id>
      <agent_name>Crystal Bomer</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>3</agent_id>
      <agent_name>Andre Porter</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>2</agency_id>
    <agency_name>Doherty Ins Agency Inc.</agency_name>
    <agency_code>AGT10001</agency_code>
    <AgentInfo>
      <agent_id>4</agent_id>
      <agent_name>Pam Rowley</agent_name>
      <agency_id>2</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>5</agent_id>
      <agent_name>Catherine Brunelle</agent_name>
      <agency_id>2</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>3</agency_id>
    <agency_name>Doherty-White Insurance Agency,Inc.</agency_name>
    <agency_code>AGT10012</agency_code>
    <AgentInfo>
      <agent_id>6</agent_id>
      <agent_name>Rita Belmonte</agent_name>
      <agency_id>3</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>7</agent_id>
      <agent_name>Cliff W Adams</agent_name>
      <agency_id>3</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>4</agency_id>
    <agency_name>John F. Dolan AGT10034</agency_name>
    <agency_code>AGT10034</agency_code>
    <AgentInfo>
      <agent_id>8</agent_id>
      <agent_name>Diane LeBlanc</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>9</agent_id>
      <agent_name>Sheila M. Doherty</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>10</agent_id>
      <agent_name>John F Dolan</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>11</agent_id>
      <agent_name>Ed Juliano</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>5</agency_id>
    <agency_name>P.S. Dolan InsuranceUSE AGT 59771</agency_name>
    <agency_code>AGT10045</agency_code>
    <AgentInfo>
      <agent_id>12</agent_id>
      <agent_name>Harvey Doren</agent_name>
      <agency_id>5</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>13</agent_id>
      <agent_name>Susan Clancy</agent_name>
      <agency_id>5</agency_id>
    </AgentInfo>
  </AgencyInfo>
</Agencies>

You can also specify default namespace by using XSINIL clause as shown below:

SELECT *
FROM @tmpAgency AgencyInfo
JOIN @tmpAgent AgentInfo ON AgencyInfo.agency_id = AgentInfo.agency_id
FOR XML AUTO
	,Root('Agencies')
	,ELEMENTS XSINIL

OUTPUT:

<Agencies xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo>
    <agency_id>1</agency_id>
    <agency_name>A and k Fowler Insurance Agency</agency_name>
    <agency_code>AG1011</agency_code>
    <AgentInfo>
      <agent_id>1</agent_id>
      <agent_name>Patrick McCalls</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>2</agent_id>
      <agent_name>Crystal Bomer</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>3</agent_id>
      <agent_name>Andre Porter</agent_name>
      <agency_id>1</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>2</agency_id>
    <agency_name>Doherty Ins Agency Inc.</agency_name>
    <agency_code>AGT10001</agency_code>
    <AgentInfo>
      <agent_id>4</agent_id>
      <agent_name>Pam Rowley</agent_name>
      <agency_id>2</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>5</agent_id>
      <agent_name>Catherine Brunelle</agent_name>
      <agency_id>2</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>3</agency_id>
    <agency_name>Doherty-White Insurance Agency,Inc.</agency_name>
    <agency_code>AGT10012</agency_code>
    <AgentInfo>
      <agent_id>6</agent_id>
      <agent_name>Rita Belmonte</agent_name>
      <agency_id>3</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>7</agent_id>
      <agent_name>Cliff W Adams</agent_name>
      <agency_id>3</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>4</agency_id>
    <agency_name>John F. Dolan AGT10034</agency_name>
    <agency_code>AGT10034</agency_code>
    <AgentInfo>
      <agent_id>8</agent_id>
      <agent_name>Diane LeBlanc</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>9</agent_id>
      <agent_name>Sheila M. Doherty</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>10</agent_id>
      <agent_name>John F Dolan</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>11</agent_id>
      <agent_name>Ed Juliano</agent_name>
      <agency_id>4</agency_id>
    </AgentInfo>
  </AgencyInfo>
  <AgencyInfo>
    <agency_id>5</agency_id>
    <agency_name>P.S. Dolan InsuranceUSE AGT 59771</agency_name>
    <agency_code>AGT10045</agency_code>
    <AgentInfo>
      <agent_id>12</agent_id>
      <agent_name>Harvey Doren</agent_name>
      <agency_id>5</agency_id>
    </AgentInfo>
    <AgentInfo>
      <agent_id>13</agent_id>
      <agent_name>Susan Clancy</agent_name>
      <agency_id>5</agency_id>
    </AgentInfo>
  </AgencyInfo>
</Agencies>

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

Different ways to concatenate strings in c# | Top 6 Ways to…

AUTO Mode with FOR XML

Related Posts:

Leave a Reply Cancel reply

Please disable your adblocker or whitelist this site!