raw mode with for xml path

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

There are four modes supported by FOR XML clause:

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

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 have to simply append FOR XML and RAW keyword to your SELECT statement, as shown in the following example:

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:

Creating simple XML using RAW mode :

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW

OUTPUT:

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

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.

You can also rename the element <row> with any other name also, as the following example shown as below:

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW('Agency')

OUTPUT:

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

You can also specify Root element which wrap up all the other elements (child elements). To create a root element, add the ROOT keyword to your FOR XML clause:

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

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW ('Agency'),Root('Agencies')

OUTPUT:

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

The examples I have shown, are all about the column values are as become attributes. However, you can also specify the column values as elements by using ELEMENTS keyword as shown below:

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW('Agency')
	,Root('Agencies')
	,ELEMENTS

OUTPUT:

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

You can also assign default schema instance by using XSINIL keyword

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW('Agency')
	,Root('Agencies')
	,ELEMENTS XSINIL

OUTPUT:

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

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 *
FROM @tmpAgency AgencyInfo
FOR XML RAW('Agency')
	,Root('Agencies')
	,XMLSCHEMA

OUTPUT:

<Agencies>
  <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="Agency">
      <xsd:complexType>
        <xsd:attribute name="agency_id" type="sqltypes:int" />
        <xsd:attribute name="agency_name">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="50" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:attribute>
        <xsd:attribute name="agency_code">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="50" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:attribute>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <Agency xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" agency_id="1" agency_name="A and k Fowler Insurance Agency" agency_code="AG1011" />
  <Agency xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" agency_id="2" agency_name="Doherty Ins Agency Inc." agency_code="AGT10001" />
  <Agency xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" agency_id="3" agency_name="Doherty-White Insurance Agency,Inc." agency_code="AGT10012" />
  <Agency xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" agency_id="4" agency_name="John F. Dolan AGT10034" agency_code="AGT10034" />
  <Agency xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" agency_id="5" agency_name="P.S. Dolan InsuranceUSE AGT 59771" agency_code="AGT10045" />
</Agencies>

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:technothirsty.com):

SELECT *
FROM @tmpAgency AgencyInfo
FOR XML RAW('Agency')
	,Root('Agencies')
	,XMLSCHEMA('urn:technothirsty.com')

OUTPUT:

<Agencies>
  <xsd:schema targetNamespace="urn:technothirsty.com" 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="Agency">
      <xsd:complexType>
        <xsd:attribute name="agency_id" type="sqltypes:int" />
        <xsd:attribute name="agency_name">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="50" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:attribute>
        <xsd:attribute name="agency_code">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="50" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:attribute>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <Agency xmlns="urn:technothirsty.com" agency_id="1" agency_name="A and k Fowler Insurance Agency" agency_code="AG1011" />
  <Agency xmlns="urn:technothirsty.com" agency_id="2" agency_name="Doherty Ins Agency Inc." agency_code="AGT10001" />
  <Agency xmlns="urn:technothirsty.com" agency_id="3" agency_name="Doherty-White Insurance Agency,Inc." agency_code="AGT10012" />
  <Agency xmlns="urn:technothirsty.com" agency_id="4" agency_name="John F. Dolan AGT10034" agency_code="AGT10034" />
  <Agency xmlns="urn:technothirsty.com" agency_id="5" agency_name="P.S. Dolan InsuranceUSE AGT 59771" agency_code="AGT10045" />
</Agencies>

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

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

6 comments

Aditya says:

October 8, 2015 at 12:45 pm

Really nice article. Keep sharing Knowledge. 🙂

Log in to Reply
1. Nehal Chauhan says:
  
  October 9, 2015 at 10:44 am
  
  Yes I will and Thank you.
  
  Log in to Reply
Abhay Makadia says:

October 7, 2015 at 1:12 pm

Really helpful 🙂

Log in to Reply
1. Nehal Chauhan says:
  
  October 7, 2015 at 3:10 pm
  
  Thanks Abhay 🙂
  
  Log in to Reply
Bhavin Pokiya says:

October 1, 2015 at 7:36 pm

Nice to see you on technothirsty. Really nice article. Keep sharing Knowledge. 🙂

Log in to Reply
1. Nehal Chauhan says:
  
  October 7, 2015 at 3:09 pm
  
  Thanks Bhavin and I assure you that I’ll keep sharing knowledge. 🙂
  
  Log in to Reply

RAW Mode with FOR XML

Related Posts:

6 comments

Leave a Reply Cancel reply

Please disable your adblocker or whitelist this site!