PATH Mode with FOR XML in sql server with an example

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 PATH Mode with FOR XML in SQL Server”.

There are four modes supported by FOR XML clause:

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

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:

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 *
FROM @tmpAgency
FOR XML PATH('Retailer')
	,TYPE

OUTPUT:

<Retailer>
  <agency_id>1</agency_id>
  <agency_name>A and k Fowler Insurance Agency</agency_name>
  <agency_code>AG1011</agency_code>
</Retailer>
<Retailer>
  <agency_id>2</agency_id>
  <agency_name>Doherty Ins Agency Inc.</agency_name>
  <agency_code>AGT10001</agency_code>
</Retailer>
<Retailer>
  <agency_id>3</agency_id>
  <agency_name>Doherty-White Insurance Agency,Inc.</agency_name>
  <agency_code>AGT10012</agency_code>
</Retailer>
<Retailer>
  <agency_id>4</agency_id>
  <agency_name>John F. Dolan AGT10034</agency_name>
  <agency_code>AGT10034</agency_code>
</Retailer>
<Retailer>
  <agency_id>5</agency_id>
  <agency_name>P.S. Dolan InsuranceUSE AGT 59771</agency_name>
  <agency_code>AGT10045</agency_code>
</Retailer>

You can also assign Namespaces and schema instance by using XMLNAMESPACES Clause.

;WITH XMLNAMESPACES (
	'http://www.w3.org/2001/XMLSchema-instance' AS xsi
	,'http://www.abc.com/Retailer' AS p1
	) SELECT agency_name
AS 'Agency_Name'
	,agency_id 'Agency_Id'
	,agency_code 'Agency_Code' FROM @tmpAgency FOR XML PATH ('p1:Retailer')
	,TYPE

OUTPUT:

<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Agency_Name>A and k Fowler Insurance Agency</Agency_Name>
  <Agency_Id>1</Agency_Id>
  <Agency_Code>AG1011</Agency_Code>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Agency_Name>Doherty Ins Agency Inc.</Agency_Name>
  <Agency_Id>2</Agency_Id>
  <Agency_Code>AGT10001</Agency_Code>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Agency_Name>Doherty-White Insurance Agency,Inc.</Agency_Name>
  <Agency_Id>3</Agency_Id>
  <Agency_Code>AGT10012</Agency_Code>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Agency_Name>John F. Dolan AGT10034</Agency_Name>
  <Agency_Id>4</Agency_Id>
  <Agency_Code>AGT10034</Agency_Code>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Agency_Name>P.S. Dolan InsuranceUSE AGT 59771</Agency_Name>
  <Agency_Id>5</Agency_Id>
  <Agency_Code>AGT10045</Agency_Code>
</p1:Retailer>

XML using namespace and tags with attributes

;WITH XMLNAMESPACES (
	'http://www.w3.org/2001/XMLSchema-instance' AS xsi
	,'http://www.abc.com/Retailer' AS p1
	)
SELECT agency_name AS 'AgencyInfo/@Agency_Name'
	,agency_id 'AgencyInfo/Agency_Id'
	,agency_code 'AgencyInfo/Agency_Code'
	FROM @tmpAgency
FOR XML PATH ('p1:Retailer'),TYPE

OUTPUT:

<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo Agency_Name="A and k Fowler Insurance Agency">
    <Agency_Id>1</Agency_Id>
    <Agency_Code>AG1011</Agency_Code>
  </AgencyInfo>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo Agency_Name="Doherty Ins Agency Inc.">
    <Agency_Id>2</Agency_Id>
    <Agency_Code>AGT10001</Agency_Code>
  </AgencyInfo>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo Agency_Name="Doherty-White Insurance Agency,Inc.">
    <Agency_Id>3</Agency_Id>
    <Agency_Code>AGT10012</Agency_Code>
  </AgencyInfo>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo Agency_Name="John F. Dolan AGT10034">
    <Agency_Id>4</Agency_Id>
    <Agency_Code>AGT10034</Agency_Code>
  </AgencyInfo>
</p1:Retailer>
<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AgencyInfo Agency_Name="P.S. Dolan InsuranceUSE AGT 59771">
    <Agency_Id>5</Agency_Id>
    <Agency_Code>AGT10045</Agency_Code>
  </AgencyInfo>
</p1:Retailer>

XML using namespace and tags with attributes including in One Root Element

;WITH XMLNAMESPACES (
	'http://www.w3.org/2001/XMLSchema-instance' AS xsi
	,'http://www.abc.com/Retailer' AS p1
	)
SELECT agency_name AS 'p1:AgencyInfo/Agency_Name'
	,agency_id 'p1:AgencyInfo/Agency_Id'
	,agency_code 'p1:AgencyInfo/Agency_Code' FROM @tmpAgency FOR XML PATH ('')
	,type
	,root ('p1:Retailer')

OUTPUT:

<p1:Retailer xmlns:p1="http://www.abc.com/Retailer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <p1:AgencyInfo>
    <Agency_Name>A and k Fowler Insurance Agency</Agency_Name>
    <Agency_Id>1</Agency_Id>
    <Agency_Code>AG1011</Agency_Code>
  </p1:AgencyInfo>
  <p1:AgencyInfo>
    <Agency_Name>Doherty Ins Agency Inc.</Agency_Name>
    <Agency_Id>2</Agency_Id>
    <Agency_Code>AGT10001</Agency_Code>
  </p1:AgencyInfo>
  <p1:AgencyInfo>
    <Agency_Name>Doherty-White Insurance Agency,Inc.</Agency_Name>
    <Agency_Id>3</Agency_Id>
    <Agency_Code>AGT10012</Agency_Code>
  </p1:AgencyInfo>
  <p1:AgencyInfo>
    <Agency_Name>John F. Dolan AGT10034</Agency_Name>
    <Agency_Id>4</Agency_Id>
    <Agency_Code>AGT10034</Agency_Code>
  </p1:AgencyInfo>
  <p1:AgencyInfo>
    <Agency_Name>P.S. Dolan InsuranceUSE AGT 59771</Agency_Name>
    <Agency_Id>5</Agency_Id>
    <Agency_Code>AGT10045</Agency_Code>
  </p1:AgencyInfo>
</p1:Retailer>

As these preceding examples demonstrate, the PATH Mode with FOR XML provides a relatively easy way to define elements and attributes in your XML result set.

If you have any questions for whatever I have explained here then feel free to comment.

PATH Mode with FOR XML

Leave a Reply Cancel reply

Please disable your adblocker or whitelist this site!