SQL Server - XML Examples(Xpath,Xquery,XML DML,XML Index,XML Schema)

Example 1:

Mostly we need  XML in this format,i.e column names with their values enclosed within their column name tag

Consider below XML needs to be generated for the below mentioned sample table definition


<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
<field Name="ID">2</field>
<field Name="Name">Sunny</field>
<field Name="Age">24</field>
<field Name="Sex">Female</field>
</Employee>


DECLARE @Employee TABLE
(ID INT,
Name VARCHAR(100),
Age INT,
Sex VARCHAR(50))

INSERT @Employee SELECT 1,'Sathya',25,'Male'
INSERT @Employee SELECT 2,'Sunny',24,'Female'


DECLARE @xmldata XML
SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH (''))

SET @xmldata = (
SELECT ColumnName AS "@Name",
       ColumnValue AS "text()"
FROM(
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('field'),root('Employee'))

SELECT @xmldata



Example 2:

If XML is stored as NTEXT and if we are required to make changes in the node values of that XML

Sample XML,



<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
</Employee>



Consider we need to change like, <field Name="ID">1</field> to <field Name="EmployeeId">001</field>





DECLARE @TMP TABLE (XMLText NTEXT)

INSERT @TMP SELECT '<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
</Employee>'

DECLARE @Xmldata XML
SET @Xmldata = (SELECT XMLText FROM @TMP)
SELECT @Xmldata XMLData

SET @Xmldata.modify('replace value of (/Employee/field[1]/text())[1] with "001"')
SET @Xmldata.modify('replace value of (/Employee/field[1]/@Name)[1] with "EmployeeId"')

UPDATE @TMP SET XMLText = CONVERT(NVARCHAR(MAX),@Xmldata)

SELECT * FROM @TMP


Example 3:

In this example, we are going to see, how we can generate XML in the below mentioned format from relational data .


<row>
  <column>1</column>
  <column>1</column>
</row>
<row>
  <column>2</column>
  <column>2</column>
</row>

Here is an example :


--Sample data 
  
DECLARE @Temp TABLE (Id1 INT,Id2 INT)
INSERT @Temp SELECT 1,1
INSERT @Temp SELECT 2,2
SELECT * FROM @Temp
  
--If we mention same alias name for all columns ,all column values will be merged
  
SELECT Id1 [column],
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
/**XML result for above query 
<row>
  <column>11</column>
</row>
<row>
  <column>22</column>
</row>
  
 **/
  
--To overcome above problem 
-- Method 1 :
  
SELECT Id1 [column],
       '',
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
  
-- Method 2 :
  
SELECT Id1 [column],
       NULL,
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
/**XML result for above Method 1 & Method 2 query 
  
<row>
  <column>1</column>
  <column>1</column>
</row>
<row>
  <column>2</column>
  <column>2</column>
</row>
  
**/

Example 4:

Avoid T (space) while generating XML using FOR XML clause

The following code shows an example on how to avoid T (space) while generating XML using FOR XML clause

Sample Data:


DECLARE @Employee TABLE
(ID INT,
Name VARCHAR(100),
DOJ DATETIME)
  
INSERT @Employee SELECT 1,'Sathya','2013-06-08 08:50:52.687'
INSERT @Employee SELECT 2,'Madhu K Nair','2008-06-08 08:50:52.687'
INSERT @Employee SELECT 3,'Vidhyasagar','2008-06-08 08:50:52.687'
  
SELECT * FROM @Employee


--you will find T(space),if you are not converting date column with proper datetime style,
SELECT * FROM  @Employee 
FOR XML PATH('Employee')

Output XML for above query :

<Employee>
  <ID>1</ID>
  <Name>Sathya</Name>
  <DOJ>2013-06-08T08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>2</ID>
  <Name>Madhu K Nair</Name>
  <DOJ>2008-06-08T08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>3</ID>
  <Name>Vidhyasagar</Name>
  <DOJ>2008-06-08T08:50:52.687</DOJ>
</Employee>

--converting date column with proper datetime style (120/121)
SELECT ID,
       Name,
       CONVERT(VARCHAR(25),DOJ,121) DOJ 
FROM @Employee 
FOR XML PATH('Employee')

Output XML for above query :

<Employee>
  <ID>1</ID>
  <Name>Sathya</Name>
  <DOJ>2013-06-08 08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>2</ID>
  <Name>Madhu K Nair</Name>
  <DOJ>2008-06-08 08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>3</ID>
  <Name>Vidhyasagar</Name>
  <DOJ>2008-06-08 08:50:52.687</DOJ>
</Employee>

Example 5:

Form nested hierarchical XML structure from relational data :
Consider below sample data :



DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subject VARCHAR(20),Marks INT)
INSERT @StudentInfo SELECT 1,'Sathya','Maths','60'
INSERT @StudentInfo SELECT 1,'Sathya','English','70'
INSERT @StudentInfo SELECT 2,'Deepak','Maths','80'
INSERT @StudentInfo SELECT 2,'Deepak','English','80'
SELECT * FROM @StudentInfo



If we need nested XML structure like shown below from above sample data :


<Students>
  <Student>
    <Name>Sathya</Name>
    <Id>1</Id>
    <Subjects>
      <Subject>
        <Name>English</Name>
        <Marks>70</Marks>
      </Subject>
      <Subject>
        <Name>Maths</Name>
        <Marks>60</Marks>
      </Subject>
    </Subjects>
  </Student>


  <Student>
    <Name>Deepak</Name>
    <Id>2</Id>
    <Subjects>
      <Subject>
        <Name>English</Name>
        <Marks>80</Marks>
      </Subject>
      <Subject>
        <Name>Maths</Name>
        <Marks>80</Marks>
      </Subject>
    </Subjects>
  </Student>
</Students>


Below query can be used :



;WITH
    Stud AS ( SELECT DISTINCT Id, Name FROM @StudentInfo ),
    Sub AS ( SELECT DISTINCT Id, Subject, Marks FROM @StudentInfo )
        SELECT
            ST.Name AS "Name",
            ST.Id AS "Id",
            (
            SELECT
                SU.Subject AS "Name",
                SU.Marks AS "Marks"
            FROM Sub SU
            WHERE ST.Id = SU.Id
            FOR XML PATH('Subject'), ROOT('Subjects'), TYPE
            )
        FROM Stud ST
        FOR XML PATH('Student'), ROOT('Students'), TYPE




I will keep updating this post with many examples.....


See Also:

No comments: