Please check my previous article @
XML in SQL Server Part 1
The for Section of the select Statement
The for section of the select statement is used to modify the relational structure of an XML document. Compared to the previous version of SQL Server, the capabilities of this option have been greatly expanded.
The general format of using the for option is the following:
[ for { browse | <xml> } ]
<xml> ::=
xml
{
{ raw [ ( 'ElementName' ) ] | auto }
[ <CoirmonDirectives>
[ , { xmldata | xmlschema [ { 'TargetNameSpaceURI' ) ] } ]
[ , elements [ xsinil | absent ]
]
| explicit
[ <ComomonDirectives>
[ , xmldata ]
]
| path [ ( 'ElementName' ) ]
[ <CommonDirectives>
[ , elements [ xsinil | absent ] ]
]
}
<CommonDirectives> :: =
[ , binary base64 ]
[ , type ]
[ , root [ ( 'RootName' ) ] ]
The meaning of each element in the preceding description is as follows:
- Browse: This option has no relation to XML. It specifies that data can be updated when viewed with applications that use DB-Library.
- Xml: The query result is to be converted to an XML document.
- Raw [('ElementName')]: A data set is to be returned, in which each row is an element named <ElementName /> or <row />, if the parentheses are omitted.
- Auto: An XML document is formed, in which the name of a table or a view is used as the name of the element representing a row of a result data set.
- Explicit: The structure of the XML document is to be specified explicitly when defining the columns of the result data set.
- Xml data: An XML-data reduced (XDR) schema is to be joined to the XML document.
Note: An XDR schema describes elements that an XML document may contain and the attributes that can be associated with the elements. It also describes the structure of an XML document, for example, specifying the daughter elements, their order, and number. A schema determines whether an element is empty or contains text. It can also hold the default values of attributes.
- Xmlschema [ ( 'TargetNameSpaceURI' ) ]: Specifies that an XML schema definition is to be created in the XML document.
f0_i DB-Library for C is an application programming interface consisting of C functions and macros that allow an application to interact with Microsoft SQL Server 2000. Included are functions that send Transact-SQL statements to SQL Server and functions that process the results of those statements.
- Elements: The columns should be represented not by attributes but by daughter elements.
xsinil " A column that can assume NULL value should be represented by an element with the xsi:nil attribute, whose value becomes TRUE if the column value becomes NULL.
absent " If the column value is NULL, the corresponding element will not be added to the XML document.
path [ ( 'ElementName' ) ] "The path to the data for XML output.
binary base64 " The data should be returned in the binary format.
type " The query result should be returned as XML data (not as text).
root [ ( 'RootName' ) ] "A root element can be added to an XML document.
Now consider some examples of using the for option for generating an XML document.
Suppose that there is a table containing information about books in three columns: <id, author, title>. Execute the below query:
select * from dbo.books for xml path ('book')
When executing a query in the SqlQuery window, Microsoft SQL Server Management Studio allows the resulting XML document to be viewed.
The output of the above query is:
<book>
<id> 1 </id>
<author> Charles Dickens </author>
<title> Oliver Twist </title>
</book>
<book>
<id> 2 </id>
<author> Stephen King </author>
<title> The Stand </title>
</book>
<book>
<id> 3 </id>
<author> Mark Twain </author>
<title> The Mysterious Stranger </title>
</book>
It is a legible XML structure, in which each table row corresponds to a book element. The nested elements correspond to table columns.
The Openxml Function
The openxml function provides a rowset view over an XML document. The function is used with the sp_xml_preparedocument stored system procedure.
The format of the function is as follows:
openxml ( idoc int [ in] , rowpattern nvarchar [ in ] ,
[ flags byte [ in ] ] )
[ with ( SchemaDeclaration | TableName ) ]
The meaning of each element in the preceding description is as follows:
" idoc " The handle of the document in the internal (not text) XML format. The text format is converted into the internal XML format using the sp_xml_preperedocument stored system function.
" rowpattern " The pattern used to identify the nodes of an XML document.
" flags " A set of flags indicating how the rowset columns are mapped to the attributes and elements:
" 0 " Defaults to the column/attribute mapping
" 1 " Column/attribute mapping
" 2 " Column/element mapping
" 8 " Used with 1 and 2; indicates that the read data must not be copied into the @mp:xmltext meta property
" SchemaDeclaration " The format of the rowset. This has the following structure: ColNameColType [ColPattern | MetaProperty]
[, ColNameColType [ColPattern | MetaProperty]
" ColName " The name of a rowset column
" CollType " The data type of a rowset column
" ColPattern " An optional pattern specifying how the XML nodes are mapped to the rowset columns
" MetaProperty " Used to obtain metadata about the XML nodes
" TableName " The name of the table containing the schema (instead of SchemaDeclaration).
The below example shows converting of an XML document into a rowset and then adding the rowset to a table.
create table dbo.books ( id int not null, author char(20) not null, title char(20) not null )
declare @hxml int
declare @ xmldata xml
-- forming the text structure of the XML document
set @xmldata = '<Root>
<book>
<id>
1/id> <author>Stephen King</author>
<title>The Stand</title>
</book>
<book>
<id>2</id>
<author>Charles Dickens</author>
<title>Oliver Twist</title>
</book>
</Root>';
Creating an XML document; @hxml is the document handle
exec sp_xml_preparedocument @hxml output, @ xmldata;
Inserting data from the XML document into the table insert into dbo.books (id, author, title)
select * from openxml(@hxml, 'Root/book', 10)
with ( id int, author char(20), title char(20) )
An Example of Using the eventdata() Function
The eventdata() function is used with DDL triggers and returns information about the events taking place in the server or a database. The information returned in the XML format. Below shows example shows creation of a table, named log_of_events, into which information about DDL statements will be placed. After the table, a trigger is created that is activated by DDL statements and places information about these statements into the created table.
create table log_of_events (
time datetime,
name char(150),
login char(150),
event char(150),
command char(150) )
create trigger trig1 on database for DDL_DATABASE_LEVEL_EVENTS as
begin
declare @xml1 xml
set @xml1 = eventdata()
insert into log_of_events (time, name, login, event, command)
values ( getdate(), @xml1.value('data(//UserName)[1]', 'nvarchar(max)'), @xml1.value('data(//LoginName)[1]', 'nvarchar(max)'), @xml1.value('data(//EventType)[1]', 'nvarchar(max)'), @xml1.value('data(//CommandText)[1]', 'nvarchar(max)') )
end