SQL Server/T-SQL Tutorial/XML/For XML
FOR XML AUTO
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT ID, first_name
4> FROM employee
5> FOR XML AUTO, TYPE
6>
7>
8>
9>
10>
11> drop table employee;
12> GO
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
<employee ID="1" first_name="Jason"/><employee ID="2" first_name="Alison"/><employee ID="3" first_name="James"/><employe
e ID="4" first_name="Celia"/><employee ID="5" first_name="Robert"/><employee ID="6" first_name="Linda"/><employee ID="7"
first_name="Dav
(1 rows affected)
FOR XML Syntax
FOR XML extends a SELECT statement by returning the relational query results in an XML format.
FOR XML operates in four different modes: RAW, AUTO, EXPLICIT, and PATH.
In RAW mode, a single row element is generated for each row in the result set, with each column in the result converted to an attribute within the element.
The syntax for using RAW mode is as follows:
FOR XML { RAW [ ("ElementName") ] }
[ [ , BINARY BASE64 ] [ , TYPE ]
[ , ROOT [ ("RootName") ] ]
[ , { XMLDATA | XMLSCHEMA
[ ("TargetNameSpaceURI") ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ] ]
BINARY BASE64 When this option is selected, binary data is returned using Base64-encoded format.
TYPE When TYPE is designated, the query returns results in the XML data type.
ROOT [ ("RootName") ] Specifies the top-level element for the XML results.
XMLDATA When XMLDATA is used, XML-Data Reduced (XDR) schema is returned.
XMLSCHEMA [ ("TargetNameSpaceURI") ] When XMLSCHEMA is used, XSD in-line schema is returned with the data results.
ELEMENTS When ELEMENTS is used, columns are returned as sub-elements.
XSINIL In conjunction with ELEMENTS, empty elements are returned for NULL values.
ABSENT Specifies that in conjunction with ELEMENTS, elements are not created for NULL values (this behavior is the default).
The FOR XML AUTO mode creates XML elements in the results of a SELECT statement, and also automatically nests the data, based on the columns in the SELECT clause. AUTO shares the same options as RAW.
Reference from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
Select for FOR XML AUTO
3> CREATE TABLE Customers (
4> CustomerID nchar (5) NOT NULL ,
5> CompanyName nvarchar (40) NOT NULL ,
6> ContactName nvarchar (30) NULL ,
7> ContactTitle nvarchar (30) NULL ,
8> Address nvarchar (60) NULL ,
9> City nvarchar (15) NULL ,
10> Region nvarchar (15) NULL ,
11> PostalCode nvarchar (10) NULL ,
12> Country nvarchar (15) NULL ,
13> Phone nvarchar (24) NULL ,
14> Fax nvarchar (24) NULL
15> )
16> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> SELECT Customers.CustomerID,
2> Customers.rupanyName
3> FROM Customers
4> FOR XML AUTO
5> GO
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
0x440A43007500730074006F006D006500720049004400440B43006F006D00700061006E0079004E0061006D006500440943007500730074006F006D00650072007300010302010E0A00310020002000200020000202110200410043010302010E0A0032
00200020002000200002021102004D0043010302010E0A00330020002000200020000202110200480043010302010E0A00340020002000200020000202110200420043010302010E0A00350020002000200020000202110200530043010302010E0A0036
0020002000200020000202110200460043010302010E0A00370020002000200020000202110200430043010302010E0A003800200020002000
(1 rows affected)
1>
2> drop table Customers;
3> GO
1>
The FOR XML Clause
SELECT <column list>
[FROM <source table(s)>]
[WHERE <restrictive condition>]
[GROUP BY <column name or expression using a column in the SELECT list>
[HAVING <restrictive condition based on the GROUP BY results>]
[ORDER BY <column list>]
[FOR XML {RAW|AUTO|EXPLICIT}[, XMLDATA][, ELEMENTS][, BINARY base64]]
[OPTION (<query hint>, [, .n])]
13> CREATE TABLE Customers (
14> CustomerID nchar (5) NOT NULL ,
15> CompanyName nvarchar (40) NOT NULL ,
16> ContactName nvarchar (30) NULL ,
17> ContactTitle nvarchar (30) NULL ,
18> Address nvarchar (60) NULL ,
19> City nvarchar (15) NULL ,
20> Region nvarchar (15) NULL ,
21> PostalCode nvarchar (10) NULL ,
22> Country nvarchar (15) NULL ,
23> Phone nvarchar (24) NULL ,
24> Fax nvarchar (24) NULL
25> )
26> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT Customers.CustomerID,
3> Customers.rupanyName
4> FROM Customers
5> FOR XML RAW
6> GO
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
0x440A43007500730074006F006D006500720049004400440B43006F006D00700061006E0079004E0061006D006500440372006F007700010302010E0A00310020002000200020000202110200410043010302010E0A0032002000200020002000020211
02004D0043010302010E0A00330020002000200020000202110200480043010302010E0A00340020002000200020000202110200420043010302010E0A00350020002000200020000202110200530043010302010E0A0036002000200020002000020211
0200460043010302010E0A00370020002000200020000202110200430043010302010E0A003800200020002000200002021102005000430103
(1 rows affected)
1>
2> drop table Customers;
3> GO
1>