Handling XML data in SQL Server



XML :
Extensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation.

Before getting deep into XML, a little introduction to the structure of XML.


Sample XML fragment:
<?xml version = "1.0" encoding = "UTF-16"?>
<!—Student Information -->

<Studentinfo>
  <Student>
    <StudentID>1</StudentID>
    <StudentName>Sathya</StudentName>
    <CourseID>1</CourseID>
  </Student>
  <Student>
    <StudentID>2</StudentID>
    <StudentName>Deepak</StudentName>
    <CourseID>2</CourseID>
  </Student>
  <Student>
    <StudentID>3</StudentID>
    <StudentName>sathish</StudentName>
    <CourseID>3</CourseID>
  </Student>
</Studentinfo>




In the above sample XML fragment , 

<Studentinfo>        is the root node
<Student>       is the element node,so in the above XML fragment,we havee 3 element nodes.

 <StudentID>   ,
 <StudentName> ,
 <CourseID> ..  are the attribute nodes

1,sathya,1,.. are the attribute values.

<Student> is the start tag &   </Student> is the end tag of the element node.
Similarly,
<Studentinfo> is the start tag & </Studentinfo> is the end tag of root node.

To put it simple in database terminologies,consider
root node as the database name,element node as the table name,attribute node as column name & attribute values as column values.

<!—Student Information -->  are XML comments denoted by <!-- and --> delimiters

<?xml version = "1.0" encoding = "UTF-16"?> are XML processing instructions  marked by <? and ?> delimiters.

A processing instruction is a means to provide additional metadata to a processing application.


 FOR XML clause:


For getting relational data in the form of XML, FOR XML clause was introduced.



Using FOR XML clause,we can represent relational data in the form of XML in two ways:
 1.)    Attribute Centric
2.)    Element Centric







/*******************************Sample data ***********************************/

Let us create sample datas for all below XML examples:


I have created sample data considering simple data model of students,courses offered,subjects in that course & marks details.

CREATE DATABASE TEST_XML
GO


USE TEST_XML
GO


CREATE TABLE Course
(
    CourseID INTIDENTITY(1,1)NOT NULL,
    CourseName VARCHAR(200) NOT NULL,
    CONSTRAINTPK_Course PRIMARY KEY(CourseID)
 )


 INSERT INTO Course (CourseName) SELECT 'Electronics Engineering'
 INSERT INTO Course (CourseName) SELECT 'Computer Science'
 INSERT INTO Course (CourseName) SELECT 'Civil Engineering'


CREATE TABLE Student
(
      StudentID BIGINT IDENTITY(1,1)NOT NULL CONSTRAINTPK_Student PRIMARY KEY(StudentID),
     StudentName VARCHAR(200) NOT NULL,
     CourseID INT NOT NULL CONSTRAINT FK_Student_CourseID FOREIGN KEY REFERENCES Course(CourseID)
           
)


INSERT INTO Student  SELECT 'Sathya',1
INSERT INTO Student  SELECT 'Deepak',2
INSERT INTO Student  SELECT 'sathish', 3


CREATE TABLE Subject
(
      SubjectID INT IDENTITY NOT NULL CONSTRAINTPK_Subject PRIMARY KEY(SubjectID),
    SubjectName VARCHAR(200)
)




INSERT INTO Subject (SubjectName) SELECT ('Electronics and Communication')
INSERT INTO Subject (SubjectName) SELECT ('Circuit Analysis')
INSERT INTO Subject (SubjectName) SELECT ('Mobile Communication')


INSERT INTO Subject (SubjectName) SELECT ('Data Structure')
INSERT INTO Subject (SubjectName) SELECT ('Java')
INSERT INTO Subject (SubjectName) SELECT ('Database Management System')

INSERT INTO Subject (SubjectName) SELECT ('Soil Mechanics')
INSERT INTO Subject (SubjectName) SELECT ('Steel Design')
INSERT INTO Subject (SubjectName) SELECT ('Concrete Design')


CREATE TABLE CourseSubject
(
      CourseID INT CONSTRAINT FK_CourseSubject_CourseID FOREIGN KEY REFERENCES Course(CourseID),
     SubjectID INT CONSTRAINT FK_CourseSubject_SubjectID FOREIGN KEY REFERENCES Subject(SubjectID)
)


INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 1,1
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 1,2
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 1,3


INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 2,4
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 2,5
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 2,6


INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 3,7
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 3,8
INSERT INTO CourseSubject (CourseID,SubjectID) SELECT 3,9


CREATE TABLE Mark
(
      StudentID BIGINT CONSTRAINT FK_Mark_StudentID FOREIGNKEY REFERENCESStudent(StudentID),
      SubjectID INT CONSTRAINT FK_Mark_SubjectID FOREIGNKEY REFERENCES Subject(SubjectID),
      Mark INT
)


INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 1,1,75
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 1,2,80
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 1,3,70


INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 2,4,80
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 2,5,80
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 2,6,90


INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 3,7,80
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 3,8,80
INSERT INTO Mark (StudentID,SubjectID,Mark) SELECT 3,9,90


SELECT * FROM Course
SELECT * FROM Student
SELECT * FROM Subject
SELECT * FROM CourseSubject
SELECT * FROM Mark



/*******************************Sample data ***********************************/



I will show you the resultset of this below query in XML format in attribute centric & element centric




SELECT * FROM Student



Example for Attribute centric:

Execute the below query,


SELECT * FROM Student
FOR XML RAW


You will notice each row of relational data is represented by each row of XML as shown below:

OUTPUT:

<row StudentID="1" StudentName="Sathya" CourseID="1" />
<row StudentID="2" StudentName="Deepak" CourseID="2" />
<row StudentID="3" StudentName="sathish" CourseID="3" />

Example for Element centric:

Execute the below query,

SELECT * FROM Student
FOR XML RAW ,ELEMENTS

You will notice each row of relational data is represented by each XML element  fragments as shown below:

OUTPUT:

<row>
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <CourseID>1</CourseID>
</row>
<row>
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <CourseID>2</CourseID>
</row>
<row>
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <CourseID>3</CourseID>
</row>
 

Types of FOR XML clause:


FOR XML RAW
FOR XML AUTO
FOR XML EXPLICIT
FOR XML PATH

Let us see,how the resultset of below query ,which is relational data is converted to XML by using different types of FOR XML clause at the end of the query .

SELECT
        STD.StudentID   ,
        STD.StudentName ,          
        SUB.SubjectID   ,
        SUB.SubjectName ,
        M.Mark         
FROM Course  C
INNER JOIN Student STD ON STD.CourseID = C.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = C.CourseID
INNER JOIN Subject SUB ON SUB.SubjectID = CS.SubjectID
INNER JOIN Mark M ON M.StudentID = STD.StudentID AND M.SubjectID = SUB.SubjectID









FOR XML RAW:

FOR XML  clause returns attribute centric XML data by default.

Example 1 - FOR XML RAW :

SELECT
        STD.StudentID   ,
        STD.StudentName ,          
        SUB.SubjectID   ,
        SUB.SubjectName ,
        M.Mark         
FROM Course  C
INNER JOIN Student STD ON STD.CourseID = C.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = C.CourseID
INNER JOIN Subject SUB ON SUB.SubjectID = CS.SubjectID
INNER JOIN Mark M ON M.StudentID = STD.StudentID AND M.SubjectID = SUB.SubjectID
FOR XML RAW


OUTPUT:

<row StudentID="1" StudentName="Sathya" SubjectID="1" SubjectName="Electronics and Communication" Mark="75" />
<row StudentID="1" StudentName="Sathya" SubjectID="2" SubjectName="Circuit Analysis" Mark="80" />
<row StudentID="1" StudentName="Sathya" SubjectID="3" SubjectName="Mobile Communication" Mark="70" />
<row StudentID="2" StudentName="Deepak" SubjectID="4" SubjectName="Data Structure" Mark="80" />
<row StudentID="2" StudentName="Deepak" SubjectID="5" SubjectName="Java" Mark="80" />
<row StudentID="2" StudentName="Deepak" SubjectID="6" SubjectName="Database Management System" Mark="90" />
<row StudentID="3" StudentName="sathish" SubjectID="7" SubjectName="Soil Mechanics" Mark="80" />
<row StudentID="3" StudentName="sathish" SubjectID="8" SubjectName="Steel Design" Mark="80" />
<row StudentID="3" StudentName="sathish" SubjectID="9" SubjectName="Concrete Design" Mark="90" />


Example 2 - FOR XML RAW:

Step 1:

UPDATE Mark SET Mark = NULL WHERE StudentID = 3 AND SubjectID = 9


Step 2: 
Let us see, how FOR XML RAW returns element centric XML data using ELEMENTS directive and NULL is handled using XSINIL option.

SELECT
        STD.StudentID   ,
        STD.StudentName ,          
        SUB.SubjectID   ,
        SUB.SubjectName ,
        M.Mark         
FROM Course  C
INNER JOIN Student STD ON STD.CourseID = C.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = C.CourseID
INNER JOIN Subject SUB ON SUB.SubjectID = CS.SubjectID
INNER JOIN Mark M ON M.StudentID = STD.StudentID AND M.SubjectID = SUB.SubjectID
FOR XML RAW ,ELEMENTS XSINIL

OUTPUT:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <SubjectID>1</SubjectID>
  <SubjectName>Electronics and Communication</SubjectName>
  <Mark>75</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <SubjectID>2</SubjectID>
  <SubjectName>Circuit Analysis</SubjectName>
  <Mark>80</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <SubjectID>3</SubjectID>
  <SubjectName>Mobile Communication</SubjectName>
  <Mark>70</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <SubjectID>4</SubjectID>
  <SubjectName>Data Structure</SubjectName>
  <Mark>80</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <SubjectID>5</SubjectID>
  <SubjectName>Java</SubjectName>
  <Mark>80</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <SubjectID>6</SubjectID>
  <SubjectName>Database Management System</SubjectName>
  <Mark>90</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <SubjectID>7</SubjectID>
  <SubjectName>Soil Mechanics</SubjectName>
  <Mark>80</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <SubjectID>8</SubjectID>
  <SubjectName>Steel Design</SubjectName>
  <Mark>80</Mark>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <SubjectID>9</SubjectID>
  <SubjectName>Concrete Design</SubjectName>
  <Markxsi:nil="true" />
</row>

FOR XML AUTO:

Used to return query resultset as nested XML elements.

Similar to FOR XML RAW,but FOR XML AUTO automatically takes element node name as table alias name & attribute node name as column name in SELECT statement.

FOR XML AUTO also return nested attribute centric XML data by default.

EXAMPLE – 1 :FOR XML AUTO

SELECT
        STUDENT.StudentID   ,
        STUDENT.StudentName ,      
        SUBJECT.SubjectID   ,
        SUBJECT.SubjectName ,
        MARK.Mark         
FROM Course  COURSE
INNER JOIN Student STUDENT ON STUDENT.CourseID = COURSE.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = COURSE.CourseID
INNER JOIN Subject SUBJECT ON SUBJECT.SubjectID = CS.SubjectID
INNER JOIN Mark MARK ON MARK.StudentID = STUDENT.StudentID AND MARK.SubjectID = SUBJECT.SubjectID
FOR XML AUTO

OUTPUT:

<STUDENT StudentID="1" StudentName="Sathya">
  <SUBJECTSubjectID="1" SubjectName="Electronics and Communication">
    <MARKMark="75" />
  </SUBJECT>
  <SUBJECTSubjectID="2" SubjectName="Circuit Analysis">
    <MARKMark="80" />
  </SUBJECT>
  <SUBJECTSubjectID="3" SubjectName="Mobile Communication">
    <MARKMark="70" />
  </SUBJECT>
</STUDENT>
<STUDENT StudentID="2" StudentName="Deepak">
  <SUBJECTSubjectID="4" SubjectName="Data Structure">
    <MARKMark="80" />
  </SUBJECT>
  <SUBJECTSubjectID="5" SubjectName="Java">
    <MARKMark="80" />
  </SUBJECT>
  <SUBJECTSubjectID="6" SubjectName="Database Management System">
    <MARKMark="90" />
  </SUBJECT>
</STUDENT>
<STUDENT StudentID="3" StudentName="sathish">
  <SUBJECTSubjectID="7" SubjectName="Soil Mechanics">
    <MARKMark="80" />
  </SUBJECT>
  <SUBJECTSubjectID="8" SubjectName="Steel Design">
    <MARKMark="80" />
  </SUBJECT>
  <SUBJECTSubjectID="9" SubjectName="Concrete Design">
    <MARK/>
  </SUBJECT>
</STUDENT>


EXAMPLE – 2 :FOR XML AUTO

SELECT
        STUDENT.StudentID   ,
        STUDENT.StudentName ,      
        SUBJECT.SubjectID   ,
        SUBJECT.SubjectName ,
        MARK.Mark         
FROM Course  COURSE
INNER JOIN Student STUDENT ON STUDENT.CourseID = COURSE.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = COURSE.CourseID
INNER JOIN Subject SUBJECT ON SUBJECT.SubjectID = CS.SubjectID
INNER JOIN Mark MARK ON MARK.StudentID = STUDENT.StudentID AND MARK.SubjectID =SUBJECT.SubjectID
FOR XML AUTO,ELEMENTS XSINIL

OUTPUT:

<STUDENT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <SUBJECT>
    <SubjectID>1</SubjectID>
    <SubjectName>Electronics and Communication</SubjectName>
    <MARK>
      <Mark>75</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>2</SubjectID>
    <SubjectName>Circuit Analysis</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>3</SubjectID>
    <SubjectName>Mobile Communication</SubjectName>
    <MARK>
      <Mark>70</Mark>
    </MARK>
  </SUBJECT>
</STUDENT>
<STUDENT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <SUBJECT>
    <SubjectID>4</SubjectID>
    <SubjectName>Data Structure</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>5</SubjectID>
    <SubjectName>Java</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>6</SubjectID>
    <SubjectName>Database Management System</SubjectName>
    <MARK>
      <Mark>90</Mark>
    </MARK>
  </SUBJECT>
</STUDENT>
<STUDENT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <SUBJECT>
    <SubjectID>7</SubjectID>
    <SubjectName>Soil Mechanics</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>8</SubjectID>
    <SubjectName>Steel Design</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>9</SubjectID>
    <SubjectName>Concrete Design</SubjectName>
    <MARK>
      <Markxsi:nil="true" />
    </MARK>
  </SUBJECT>
</STUDENT>

EXAMPLE – 3: FOR XML AUTO :

SELECT
        STUDENT.StudentID   ,
        STUDENT.StudentName ,      
        SUBJECT.SubjectID   ,
        SUBJECT.SubjectName ,
        MARK.Mark         
FROM Course  COURSE
INNER JOIN Student STUDENT ON STUDENT.CourseID = COURSE.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = COURSE.CourseID
INNER JOIN Subject SUBJECT ON SUBJECT.SubjectID = CS.SubjectID
INNER JOIN Mark MARK ON MARK.StudentID = STUDENT.StudentID AND  MARK.SubjectID = SUBJECT.SubjectID
FOR XML AUTO,ELEMENTS XSINIL,TYPE,XMLSCHEMA

o   The TYPE option, when specified, returns your FOR XML result as an xml data type instance.

o   XMLSCHEMA to get schema along with XML result.

OUTPUT:

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" elementFormDefault="qualified">
  <xsd:importnamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:elementname="STUDENT">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:elementname="StudentID" type="sqltypes:bigint" nillable="1" />
        <xsd:elementname="StudentName" nillable="1">
          <xsd:simpleType>
            <xsd:restrictionbase="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="200" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:elementref="schema:SUBJECT" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:elementname="SUBJECT">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:elementname="SubjectID" type="sqltypes:int" nillable="1" />
        <xsd:elementname="SubjectName" nillable="1">
          <xsd:simpleType>
            <xsd:restrictionbase="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="200" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:elementref="schema:MARK" minOccurs="0" maxOccurs="unbounded" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:elementname="MARK">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:elementname="Mark" type="sqltypes:int" nillable="1" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<STUDENT xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>1</StudentID>
  <StudentName>Sathya</StudentName>
  <SUBJECT>
    <SubjectID>1</SubjectID>
    <SubjectName>Electronics&amp;Communication</SubjectName>
    <MARK>
      <Mark>75</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>2</SubjectID>
    <SubjectName>Circuit Analysis</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>3</SubjectID>
    <SubjectName>Mobile Communication</SubjectName>
    <MARK>
      <Mark>70</Mark>
    </MARK>
  </SUBJECT>
</STUDENT>
<STUDENT xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>2</StudentID>
  <StudentName>Deepak</StudentName>
  <SUBJECT>
    <SubjectID>4</SubjectID>
    <SubjectName>Data Structure</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>5</SubjectID>
    <SubjectName>Java</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>6</SubjectID>
    <SubjectName>Database Management System</SubjectName>
    <MARK>
      <Mark>90</Mark>
    </MARK>
  </SUBJECT>
</STUDENT>
<STUDENT xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StudentID>3</StudentID>
  <StudentName>sathish</StudentName>
  <SUBJECT>
    <SubjectID>7</SubjectID>
    <SubjectName>Soil Mechanics</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>8</SubjectID>
    <SubjectName>Steel Design</SubjectName>
    <MARK>
      <Mark>80</Mark>
    </MARK>
  </SUBJECT>
  <SUBJECT>
    <SubjectID>9</SubjectID>
    <SubjectName>Concrete Design</SubjectName>
    <MARK>
      <Markxsi:nil="true" />
    </MARK>
  </SUBJECT> 

  </STUDENT>




FOR XML EXPICIT:

Using FOR XML EXPLICIT is little different,we can define the path (node structure)of output XML .

But its complicated because we have to use nested SELECT statements using UNION ALL to define path of output XML.

EXAMPLE FOR XML EXPLICIT :

IF you have a look at the below example,
 Tag & Parent are used to nest subsequent SELECT statements.

In the first SELECT statement itself we can define the path (node structure) of output XML in the below format
ElementName!TagNumber!AttributeName!Directive

Each SELECT statement represents an Element node,so we are assigning values to each SELECT statement accordingly.

SELECT 1 AS Tag,
NULL AS Parent,
0 AS[STUDENTINFO!1!SORT!HIDE],
NULL AS [STUDENTINFO!1!],
NULL AS [student!2!ID],
NULL AS [student!2!name],
NULL AS [subject!3!ID],
NULL AS [subject!3!Name]
UNION ALL
SELECT 2 AS Tag,
1 ASParent,
STD.StudentID + 1,
NULL,
STD.StudentID AS [student!2!ID],
STD.StudentName AS [student!2!name],
 NULL ,
 NULL
FROM  Student STD
UNION ALL
SELECT 3 AS Tag,
2 ASParent,
STD.StudentID + 1,
NULL,
NULL,
NULL,
SUB.SubjectID AS [subject!3!ID],
SUB.SubjectName As [subject!3!Name]
FROM Course  C
INNER JOIN Student STD ON STD.CourseID = C.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = C.CourseID
INNER JOIN Subject SUB ON SUB.SubjectID = CS.SubjectID
INNER JOIN Mark M ON M.StudentID = STD.StudentID AND M.SubjectID = SUB.SubjectID
ORDER BY [STUDENTINFO!1!SORT!HIDE]
FOR XML EXPLICIT


In the above example [STUDENTINFO!1!SORT!HIDE] is nothing but used to sort the elements and HIDE directive is used to hide the sorting column.

OUTPUT:

<STUDENTINFO>
  <studentID="1" name="Sathya">
    <subjectID="1" Name="Electronics and Communication" />
    <subjectID="2" Name="Circuit Analysis" />
    <subjectID="3" Name="Mobile Communication" />
  </student>
  <studentID="2" name="Deepak">
    <subjectID="4" Name="Data Structure" />
    <subjectID="5" Name="Java" />
    <subjectID="6" Name="Database Management System" />
  </student>
  <studentID="3" name="sathish">
    <subjectID="7" Name="Soil Mechanics" />
    <subjectID="8" Name="Steel Design" />
    <subjectID="9" Name="Concrete Design" />
  </student>
</STUDENTINFO>

FOR XML PATH:

Though both FOR XML EXPLICIT & XML PATH perform same operation. 
FOR XML PATH is very simple in defining path(node structure)of XML output.

Using FOR XML PATH,we can define path(node structure)of XML output in single SELECT as shown below:

SELECT
        STD.StudentID    "@StudentID" ,
        STD.StudentName  "@StudentName",       
        SUB.SubjectID    "Student/ID",
        SUB.SubjectName  "Student/Name",
        M.Mark           "Student/Mark"
FROM Course  C
INNER JOIN Student STD ON STD.CourseID = C.CourseID
INNER JOIN CourseSubject CS ON CS.CourseID = C.CourseID
INNER JOIN Subject SUB ON SUB.SubjectID = CS.SubjectID
INNER JOIN Mark M ON M.StudentID = STD.StudentID AND M.SubjectID = SUB.SubjectID
FOR XML PATH ('Student')


@StudentID - @ indicates that it should be attribute.


OUTPUT:

<Student StudentID="1" StudentName="Sathya">
  <Student>
    <ID>1</ID>
    <Name>Electronics and Communication</Name>
    <Mark>75</Mark>
  </Student>
</Student>
<Student StudentID="1" StudentName="Sathya">
  <Student>
    <ID>2</ID>
    <Name>Circuit Analysis</Name>
    <Mark>80</Mark>
  </Student>
</Student>
<Student StudentID="1" StudentName="Sathya">
  <Student>
    <ID>3</ID>
    <Name>Mobile Communication</Name>
    <Mark>70</Mark>
  </Student>
</Student>
<Student StudentID="2" StudentName="Deepak">
  <Student>
    <ID>4</ID>
    <Name>Data Structure</Name>
    <Mark>80</Mark>
  </Student>
</Student>
<Student StudentID="2" StudentName="Deepak">
  <Student>
    <ID>5</ID>
    <Name>Java</Name>
    <Mark>80</Mark>
  </Student>
</Student>
<Student StudentID="2" StudentName="Deepak">
  <Student>
    <ID>6</ID>
    <Name>Database Management System</Name>
    <Mark>90</Mark>
  </Student>
</Student>
<Student StudentID="3" StudentName="sathish">
  <Student>
    <ID>7</ID>
    <Name>Soil Mechanics</Name>
    <Mark>80</Mark>
  </Student>
</Student>
<Student StudentID="3" StudentName="sathish">
  <Student>
    <ID>8</ID>
    <Name>Steel Design</Name>
    <Mark>80</Mark>
  </Student>
</Student>
<Student StudentID="3" StudentName="sathish">
  <Student>
    <ID>9</ID>
    <Name>Concrete Design</Name>
  </Student>
</Student>
 

 

See Also:





No comments: