Transcript No Slide Title

Chapter 18
How to work with XML
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
 Given the specifications for a table that requires an xml data type,
create the table and then add and retrieve XML data from the
table.
 Use the XML Editor to display the XML returned by a query.
 Use the XML Editor to create an XML Schema Definition for an
XML document.
 Use the methods of the xml type to work with XML data.
 Given an XML Schema Definition, code a statement that adds it
to a database.
 Use an XML Schema Definition to provide XML validation for a
column or variable.
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 2
Objectives (continued)
Knowledge
 Describe the structure and content of an XML document.
 Describe the use of an XML schema.
 Describe the use of an XQuery.
 Describe the use of XML DML.
 Describe XML validation.
 Describe the basic function of the FOR XML clause of the
SELECT statement.
 Describe the basic function of the OPENXML statement.
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 3
An XML document
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2008-09-23T09:46:40.773</PostTime>
<SPID>55</SPID>
<ServerName>JOEL3\SQLEXPRESS</ServerName>
<LoginName>JOEL3\Joel Murach</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AP</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>VendorsTest</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON"
ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>
CREATE TABLE VendorsTest
(VendorID int, VendorName varchar(50))
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 4
An XML Schema Definition (XSD)
<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="EVENT_INSTANCE">
<xs:complexType>
<xs:sequence>
<xs:element name="EventType" type="xs:string" />
<xs:element name="PostTime" type="xs:string" />
<xs:element name="SPID" type="xs:unsignedByte" />
<xs:element name="ServerName" type="xs:string" />
<xs:element name="LoginName" type="xs:string" />
<xs:element name="UserName" type="xs:string" />
<xs:element name="DatabaseName" type="xs:string" />
<xs:element name="SchemaName" type="xs:string" />
<xs:element name="ObjectName" type="xs:string" />
<xs:element name="ObjectType" type="xs:string" />
<xs:element name="TSQLCommand">
<xs:complexType>
<xs:sequence>
<xs:element name="SetOptions">
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 5
An XML Schema Definition (cont.)
<xs:complexType>
<xs:attribute name="ANSI_NULLS"
type="xs:string" use="required" />
<xs:attribute name="ANSI_NULL_DEFAULT"
type="xs:string" use="required" />
<xs:attribute name="ANSI_PADDING"
type="xs:string" use="required" />
<xs:attribute name="QUOTED_IDENTIFIER"
type="xs:string" use="required" />
<xs:attribute name="ENCRYPTED"
type="xs:string" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="CommandText" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 6
A log table with a column of the xml data type
CREATE TABLE DDLActivityLog
(EventID int NOT NULL IDENTITY PRIMARY KEY,
EventData xml NOT NULL)
A trigger that inserts an XML document into the
xml column
CREATE TRIGGER Database_CreateTable_DropTable
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE
AS
DECLARE @EventData xml
SELECT @EventData = EVENTDATA()
INSERT INTO DDLActivityLog VALUES (@EventData)
A CREATE TABLE statement that fires the trigger
CREATE TABLE VendorsTest
(VendorID int, VendorName varchar(50))
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 7
A SELECT statement that retrieves data from the
table
SELECT * FROM DDLActivityLog
The result set
An INSERT statement that inserts a row into the
table
INSERT INTO DDLActivityLog VALUES
('<root><element1>test</element1></root>')
Another INSERT statement that inserts a row into
the table
INSERT INTO DDLActivityLog VALUES ('this is not xml')
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 8
A result set that returns XML data
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 9
An xml data type displayed in the XML Editor
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 10
The methods of the xml type
Method
query(XQuery)
exist(XQuery)
value(XQuery,
SqlType)
modify(XML_DML)
nodes(XQuery)
AS Table(Column)
Murach’s SQL Server 2008, C18
Description
Performs an XQuery and returns an xml
type that contains the XML fragment
specified by the XQuery.
Returns a value of 1 if the XQuery returns
a result set. Otherwise, returns a value of 0.
Performs an XQuery and returns a scalar
value of the specified SQL data type.
Uses an XML DML statement to insert,
update, or delete nodes from the current
xml type.
Splits the nodes of the current xml data
type into rows.
© 2008, Mike Murach & Associates, Inc.
Slide 11
The simplified XQuery syntax
(/rootElement/element1/element2/@attribute)
[elementOrAttributeNumber]
A SELECT statement that uses the query method
SELECT
EventData.query('/EVENT_INSTANCE/TSQLCommand/SetOptions')
AS SetOptions
FROM DDLActivityLog
WHERE EventID = 1
The XML data that’s returned
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 12
A script that uses the exist method
DECLARE @EventData xml
SELECT @EventData = EventData
FROM DDLActivityLog
WHERE EventID = 1
IF @EventData.exist('/EVENT_INSTANCE/EventType') = 1
PRINT 'The EventType element exists and contains data.'
The response from the system
The EventType element exists and contains data.
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 13
Rules for coding an XQuery
 Start each XQuery with a front slash (/).
 Use a front slash (/) to separate elements and attributes.
 Use an at symbol (@) to identify attributes.
 When necessary, use square brackets ([]) to specify an element or
attribute instance.
 If you specify an element or attribute instance, you must code
parentheses around the path specification for the element or
attribute. Otherwise the parentheses are optional.
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 14
A SELECT statement that uses the value method
SELECT
EventData.value(
'(/EVENT_INSTANCE/EventType)[1]',
'varchar(40)') AS EventType,
EventData.value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@ANSI_NULLS)[1]',
'varchar(40)') AS ANSI_NULLS_SETTING
FROM DDLActivityLog
WHERE EventID = 1
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 15
A SET statement that uses the modify method
DECLARE @EventData xml
SELECT @EventData = EventData
FROM DDLActivityLog
WHERE EventID = 1
SET @EventData.modify
('replace value of (/EVENT_INSTANCE/EventType/text())[1]
with "TEST"')
SELECT @EventData AS ModifiedEventData
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 16
An example that parses an xml column into a
relational result set
SELECT
EventID,
EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(40)')
AS EventType,
EventData.value('(/EVENT_INSTANCE/PostTime)[1]',
'smalldatetime')
AS PostTime,
EventData.value('(/EVENT_INSTANCE/LoginName)[1]',
'varchar(40)')
AS LoginName,
EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(40)')
AS ObjectName
FROM DDLActivityLog
WHERE
EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(40)')
= 'DROP_TABLE'
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 17
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 18
A log table that doesn’t use the xml data type
CREATE TABLE DDLActivityLog2
(
EventID int NOT NULL IDENTITY PRIMARY KEY,
EventType varchar(40) NOT NULL,
PostTime smalldatetime NOT NULL,
LoginName varchar(40) NOT NULL,
ObjectName varchar(40) NOT NULL
)
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 19
A trigger that parses XML data and inserts it into
the table
CREATE TRIGGER Database_CreateTable_DropTable2
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE
AS
DECLARE @EventData xml
SELECT @EventData = EVENTDATA()
INSERT INTO DDLActivityLog2 VALUES
(
@EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(40)'),
@EventData.value('(/EVENT_INSTANCE/PostTime)[1]',
'varchar(40)'),
@EventData.value('(/EVENT_INSTANCE/LoginName)[1]',
'varchar(40)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(40)')
)
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 20
A SELECT statement that retrieves data from the
table
SELECT * FROM DDLActivityLog2
WHERE EventType = 'DROP_TABLE'
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 21
The syntax for the CREATE XML SCHEMA
COLLECTION statement
CREATE XML SCHEMA COLLECTION
[database_schema_name.]xml_schema_name
AS xml_schema_expression
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 22
An example that creates a schema
CREATE XML SCHEMA COLLECTION EventDataSchema
AS
'
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="EVENT_INSTANCE">
<xs:complexType>
<xs:sequence>
<xs:element name="EventType" type="xs:string" />
<xs:element name="PostTime" type="xs:string" />
<xs:element name="SPID" type="xs:unsignedByte" />
<xs:element name="ServerName" type="xs:string" />
<xs:element name="LoginName" type="xs:string" />
<xs:element name="UserName" type="xs:string" />
<xs:element name="DatabaseName" type="xs:string" />
<xs:element name="SchemaName" type="xs:string" />
<xs:element name="ObjectName" type="xs:string" />
<xs:element name="ObjectType" type="xs:string" />
<xs:element name="TSQLCommand">
<xs:complexType>
<xs:sequence>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 23
An example that creates a schema (cont.)
<xs:element name="SetOptions">
<xs:complexType>
<xs:attribute name="ANSI_NULLS"
type="xs:string" use="required" />
<xs:attribute name="ANSI_NULL_DEFAULT"
type="xs:string" use="required" />
<xs:attribute name="ANSI_PADDING"
type="xs:string" use="required" />
<xs:attribute name="QUOTED_IDENTIFIER"
type="xs:string" use="required" />
<xs:attribute name="ENCRYPTED"
type="xs:string" use="required" />
</xs:complexType>
</xs:element>
<xs:element name="CommandText"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
'
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 24
The syntax for declaring XML schema validation
column_or_variable_name XML
([database_schema_name.]xml_schema_name)
A log table with a column that specifies an XML
schema
CREATE TABLE DDLActivityLog3
(EventID int NOT NULL IDENTITY PRIMARY KEY,
EventData xml (EventDataSchema) NOT NULL)
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 25
An INSERT statement that attempts to insert
non-XML data
INSERT INTO DDLActivityLog3 VALUES ('this is not xml')
The response from the system
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this
location, the type was defined with element only content
or with simple content.
Location: /
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 26
An INSERT statement that attempts to insert XML
data whose tags don’t match the tags in the
schema
INSERT INTO DDLActivityLog3
VALUES ('<MyRoot><MyElement>test</MyElement></MyRoot>')
The response from the system
Msg 6913, Level 16, State 1, Line 1
XML Validation: Declaration not found for element
'MyRoot'.
Location: /*:MyRoot[1]
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 27
An INSERT statement that attempts to insert XML
data that doesn’t contain all the elements
specified by the schema
DECLARE @CreateTableEvent xml
SET @CreateTableEvent = '
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
</EVENT_INSTANCE>
'
INSERT INTO DDLActivityLog3
VALUES (@CreateTableEvent)
The response from the system
Msg 6908, Level 16, State 1, Line 7
XML Validation: Invalid content. Expected element(s):
PostTime.
Location: /*:EVENT_INSTANCE[1]
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 28
The syntax for the XML_SCHEMA_NAMESPACE
function
XML_SCHEMA_NAMESPACE('database_schema_name',
'xml_schema_name')
A statement that returns the XML schema
SELECT XML_SCHEMA_NAMESPACE('dbo', 'EventDataSchema')
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 29
The syntax for the DROP XML SCHEMA
COLLECTION statement
DROP XML SCHEMA COLLECTION
[database_schema_name.]xml_schema_name
An example that drops a schema
IF
EXISTS
(SELECT * FROM sys.xml_schema_collections
WHERE name = 'EventDataSchema')
BEGIN
DROP XML SCHEMA COLLECTION EventDataSchema
END
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 30
The simplified syntax of the FOR XML clause
select_statement
FOR XML {RAW|AUTO} [, ROOT ('RootName')] [, ELEMENTS ]
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 31
A SELECT statement that uses the RAW keyword
SELECT TOP 5 VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
ORDER BY VendorName
FOR XML RAW, ROOT ('VendorInvoices')
The XML document that’s returned
<VendorInvoices>
<row VendorName="Abbey Office Furnishings"
InvoiceNumber="203339-13" InvoiceTotal="17.5000" />
<row VendorName="Bertelsmann Industry Svcs. Inc"
InvoiceNumber="509786" InvoiceTotal="6940.2500" />
<row VendorName="Blue Cross"
InvoiceNumber="547479217" InvoiceTotal="116.0000" />
<row VendorName="Blue Cross"
InvoiceNumber="547480102" InvoiceTotal="224.0000" />
<row VendorName="Blue Cross"
InvoiceNumber="547481328" InvoiceTotal="224.0000" />
</VendorInvoices>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 32
A SELECT statement that uses the AUTO keyword
SELECT TOP 5 VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors AS Vendor JOIN Invoices AS Invoice
ON Vendor.VendorID = Invoice.VendorID
ORDER BY VendorName
FOR XML AUTO, ROOT ('VendorInvoices')
The XML document that’s returned
<VendorInvoices>
<Vendor VendorName="Abbey Office Furnishings">
<Invoice InvoiceNumber="203339-13" InvoiceTotal="17.5000" />
</Vendor>
<Vendor VendorName="Bertelsmann Industry Svcs. Inc">
<Invoice InvoiceNumber="509786" InvoiceTotal="6940.2500" />
</Vendor>
<Vendor VendorName="Blue Cross">
<Invoice InvoiceNumber="547479217" InvoiceTotal="116.0000" />
<Invoice InvoiceNumber="547480102" InvoiceTotal="224.0000" />
<Invoice InvoiceNumber="547481328" InvoiceTotal="224.0000" />
</Vendor>
</VendorInvoices>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 33
A SELECT statement that uses the AUTO and
ELEMENTS keywords
SELECT TOP 5 VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors AS Vendor JOIN Invoices AS Invoice
ON Vendor.VendorID = Invoice.VendorID
ORDER BY VendorName
FOR XML AUTO, ROOT ('VendorInvoices'), ELEMENTS
The XML document that’s returned (part 1)
<VendorInvoices>
<Vendor>
<VendorName>Abbey Office Furnishings</VendorName>
<Invoice>
<InvoiceNumber>203339-13</InvoiceNumber>
<InvoiceTotal>17.5000</InvoiceTotal>
</Invoice>
</Vendor>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 34
The XML document that’s returned (part 2)
<Vendor>
<VendorName>Bertelsmann Industry Svcs. Inc</VendorName>
<Invoice>
<InvoiceNumber>509786</InvoiceNumber>
<InvoiceTotal>6940.2500</InvoiceTotal>
</Invoice>
</Vendor>
<Vendor>
<VendorName>Blue Cross</VendorName>
<Invoice>
<InvoiceNumber>547479217</InvoiceNumber>
<InvoiceTotal>116.0000</InvoiceTotal>
</Invoice>
<Invoice>
<InvoiceNumber>547480102</InvoiceNumber>
<InvoiceTotal>224.0000</InvoiceTotal>
</Invoice>
<Invoice>
<InvoiceNumber>547481328</InvoiceNumber>
<InvoiceTotal>224.0000</InvoiceTotal>
</Invoice>
</Vendor>
</VendorInvoices>
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 35
The simplified syntax for the OPENXML statement
OPENXML (xml_document_handle_int, x_path)
WITH ( table_definition )
The simplified syntax for the
sp_Xml_PrepareDocument procedure
EXEC sp_Xml_PrepareDocument xml_document_handle_int OUTPUT,
xml_document
The simplified syntax for the
sp_Xml_RemoveDocument procedure
EXEC sp_Xml_RemoveDocument xml_document_handle_int
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 36
Code that uses the OPENXML statement to parse
XML
-- Declare an int variable that's a handle for the internal
-- XML document
DECLARE @VendorInvoicesHandle int
-- Create an xml variable that stores the XML document
DECLARE @VendorInvoices xml
SET @VendorInvoices = ' xml from part 2 of 18-11 goes here '
-- Prepare the internal XML document
EXEC sp_Xml_PrepareDocument @VendorInvoicesHandle OUTPUT,
@VendorInvoices
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 37
Code that uses the OPENXML statement to parse
XML (cont.)
-- SELECT the data from the table returned by the OPENXML
-- statement
SELECT *
FROM OPENXML (@VendorInvoicesHandle,
'/Vendors/Vendor/Invoice')
WITH
(
VendorName
varchar(50) '../VendorName',
InvoiceNumber varchar(50) 'InvoiceNumber',
InvoiceTotal money
'InvoiceTotal'
)
-- Remove the internal XML document
EXEC sp_Xml_RemoveDocument @VendorInvoicesHandle
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 38
The result set
Murach’s SQL Server 2008, C18
© 2008, Mike Murach & Associates, Inc.
Slide 39