XML with SQL Query

Generating XML with SQL Query

SQL server enables you to retrieve and manipulate the result of any query as XML.

To do this you use FOR XML clause

FOR XML clause return a SQL result as XML 

It can be used in top-level queries or in sub queries.

Varieties of option available FOR XML clause to customize the XML.

Modes:-

1. RAW

1. AUTO

1. EXPLICIT

1. PATH

RAW:-  Generate one element for each row of the result set for the individual column represented as attribute.
Ex:-
      select People.Firstname,Phone.phonenumber from person.Person People
       inner join person.PersonPhone Phone
       on People.BusinessEntityID=Phone.BusinessEntityID
       for xml raw
Output:-
 <row Firstname="Raj" phonenumber="697-555-0142"/>
AUTO :- You can include the ELEMENTS option to represent columns as elements rather than as attributesMeans construct  a SELECT statement accordingly to generate a meaningful XML Element Name:
Ex:- select People.Firstname,Phone.phonenumber from person.Person People
       inner join person.PersonPhone Phone
       on People.BusinessEntityID=Phone.BusinessEntityID
       for xml Auto
Output:-<People Firstname="Raj"><Phone phonenumber="697-555-0142"/></People>
EXPLICIT :- you must construct your query so as to create a resultset with the first column named Tag and the second column named Parent. These columns create a self-join in the resultset that is used to determine the hierarchy of the created XML file.
Ex:-    select 1 as Tag,
    Null as Parent,
    People.Firstname as [Person!1!Firstname],Phone.phonenumber as [Person!1!phonenumber] from               person.Person People
       inner join person.PersonPhone Phone
       on People.BusinessEntityID=Phone.BusinessEntityID
       where People.FirstName like 'A%'
       for xml explicit
Output:-
<Person Firstname="Annik" phonenumber="499-555-0125"/><Person Firstname="Andrew" phonenumber="908-555-0159"/><Person Firstname="Alice" phonenumber="333-555-0173"/><Person Firstname="Angela" phonenumber="150-555-0194"/><Person Firstname="Anibal" phonenumber="106-555-0120"/><Person Firstname="Andy" phonenumber="118-555-0110"/><Person Firstname="Alex" phonenumber="819-555-0198"/><Person Firstname="Andrew" phonenumber="207-555-0192"/><Person Firstname="Alejandro" phonenumber="668-555-0130"/><Person Firstname="Andreas" phonenumber="181-555-0124"/><Person Firstname="A. Scott" phonenumber="992-555-0194"/><Person Firstname="Alan" phonenumber="438-555-0172"/><Person Firstname="Arvind" phonenumber="848-555-0163"/><Person Firstname="Annette" phonenumber="125-555-0196"/><Person Firstname="Ashvini" phonenumber="656-555-0119"/><Person Firstname="Amy" phonenumber="775-555-0164"/><Person Firstname="Amy" phonenumber="727-555-0115"/><Person Firstname="Anna" phonenumber="197-555-0143"/><Person Firstname="Alberto" phonenumber="780-555-0114"/><Person Firstname="Angela" phonenumber="134-555-0182"/><Person Firstname="Adam" phonenumber="726-555-0191"/><Person Firstname="Ann" phonenumber="277-555-0169"/><Person Firstname="Alexander" phonenumber="519-555-0151"/><Person Firstname="Andreas" phonenumber="795-555-0116"/><Person Firstname="Alan" phonenumber="494-555-0134"/><Person Firstname="Andy" phonenumber="944-555-0148"/><Person Firstname="Andrew" phonenumber="644-555-0111"/><Person Firstname="Anthony" phonenumber="429-555-0145"/><Person Firstname="Alice" phonenumber="221-555-0141"/><Person Firstname="Aaron" phonenumber="652-555-0115"/><Person Firstname="Amy" phonenumber="1 (11) 500 555-0133"/><Person Firstname="Alvaro" phonenumber="551-555-0155"/><Person Firstname="Alexander" phonenumber="394-555-0176"/><Person Firstname="Aidan" phonenumber="358-555-0188"/><Person Firstname="Andrew" phonenumber="576-555-0171"/><Person Firstname="Adrian" phonenumber="362-555-0121"/><Person Firstname="Ann" phonenumber="252-555-0127"/>

This Option require atleast three component<ElementName>!<TagNumber>!<AttributeName>

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

PATH :- 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. 
Ex:- select People.Firstname,Phone.phonenumber from person.Person People
       inner join person.PersonPhone Phone
       on People.BusinessEntityID=Phone.BusinessEntityID
  where People.FirstName='Raj'
       for xml path
  ('Identity'), ROOT ('Person');
Output:-
<Person><Identity><Firstname>Raj</Firstname><phonenumber>697-555-0142</phonenumber></Identity></Person>
Thanks