Example 1:
Mostly we need XML in this format,i.e column names with their values enclosed within their column name tagConsider 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 clauseThe 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.....
No comments:
Post a Comment