Manipulating XML Data. -- Switching Databases USE XMLPlay;

(1)

--- -- Total Training for SQL Server 2008

--

-- Lesson 8 - Manipulating XML Data --

-- Paul Nielsen / John Lancashire

--- --- -- Manipulating XML Data

-- Switching Databases USE XMLPlay;

IF OBJECT_ID('Customers','U') IS NOT NULL DROP TABLE Customers CREATE TABLE Customers (

CustomerID INT IDENTITY PRIMARY KEY, CustomerNumber VARCHAR(20),

Name VARCHAR(40), Phone VARCHAR(15), Street VARCHAR(40), City VARCHAR(20), State VARCHAR(10), Zip VARCHAR(10) ) GO

IF OBJECT_ID('Items','U') IS NOT NULL DROP TABLE Items CREATE TABLE Items (

ItemID INT IDENTITY PRIMARY KEY, ItemNumber VARCHAR(20),

ItemDescription VARCHAR(40) ) GO

IF OBJECT_ID('OrderHeader','U') IS NOT NULL DROP TABLE OrderHeader CREATE TABLE OrderHeader (

OrderID INT IDENTITY PRIMARY KEY, OrderNumber VARCHAR(20),

OrderDate DATETIME, CustomerID INT ) GO

(2)

IF OBJECT_ID('OrderDetails','U') IS NOT NULL DROP TABLE OrderDetails CREATE TABLE OrderDetails (

OrderDetailID INT IDENTITY PRIMARY KEY, OrderID INT,

ItemID INT, Quantity INT, Price MONEY )

IF OBJECT_ID('OrderXML','U') IS NOT NULL DROP TABLE OrderXML CREATE TABLE OrderXML (OrderID INT, ItemData XML)

GO

-- Here is some script for Insert query -- Populate Customer Table

INSERT INTO Customers (

CustomerNumber, Name, Phone, Street, City, State, Zip )

SELECT

'J001', 'Jacob Sebastian', '(999) 999-9999', '401, Jacobs Street', 'New York', 'NY', '12345'

-- Populate Items Table

INSERT INTO Items (ItemNumber, ItemDescription) SELECT 'D001','DELL XPS 1130 Laptop'

UNION ALL

SELECT 'Z001','XBOX 360 Console' -- Create order "SO101"

INSERT INTO OrderHeader( OrderNumber, OrderDate, CustomerID ) SELECT 'SO101','2009-01-23',1

-- Add Line Items

INSERT INTO OrderDetails (OrderID, ItemID, Quantity, Price) SELECT 1, 1, 1, 900

UNION ALL

SELECT 1, 2, 1, 200 -- Create order "SO102"

INSERT INTO OrderHeader( OrderNumber, OrderDate, CustomerID ) SELECT 'SO102','2009-01-24',1

-- Add Line Items

INSERT INTO OrderDetails (OrderID, ItemID, Quantity, Price)

(3)

SELECT 2, 1, 1, 900

-- And finally, some XML data

INSERT INTO OrderXML (OrderID, ItemData) SELECT 1, '

</Order > '

INSERT INTO OrderXML (OrderID, ItemData) SELECT 2, '

</Order > '

--- -- 2 Creating and using XML columns

DECLARE @t TABLE (OrderID INT,OrderData XML ) INSERT INTO @t(OrderID, OrderData)

SELECT 1,

'<CustomerNumber>1001</CustomerNumber>

<Items>

</Items>' SELECT

OrderID,

OrderData.value('CustomerNumber[1]','CHAR(4)') AS CustomerNumber FROM @t

--- -- 3 Some complex Query in XML

-- If the query needs to retrieve more than one element from the XML document stored -- in each row. Such a query needs to generate more than one row against each row -- stored in the base table. The nodes() method of the XML data type can be used to -- obtain an accessor to each element within the XML document.

-- The XML element collection returned by the nodes()method can be joined with the -- base table using the CROSS APPLY operator as shown in the following

(4)

DECLARE @t TABLE (OrderID INT,OrderData XML ) INSERT INTO @t(OrderID, OrderData)

SELECT 1,

'<CustomerNumber>1001</CustomerNumber>

<Items>

</Items>' SELECT

OrderID,

o.value('@ItemNumber','CHAR(4)') AS ItemNumber, o.value('@Quantity','INT') AS Quantity,

o.value('@Price','MONEY') AS Price FROM @t

CROSS APPLY OrderData.nodes('/Items/Item') x(o)

--- -- 4 Declaring and Using XML Variables

/*

-- Declare an XML Variable DECLARE @x XML

-- Declare a TYPED XML Variable DECLARE @x XML(CustomerSchema)

-- Declare a TYPED XML DOCUMENT Variable DECLARE @x XML(DOCUMENT CustomerSchema) -- Declare a TYPED XML CONTENT Variable DECLARE @x XML(CONTENT CustomerSchema)

*/

-- Version of the above query that operates on an XML variable DECLARE @x XML

SELECT @x= '

(5)

<Items>

</Items>' SELECT

o.value('@ItemNumber','CHAR(4)') AS ItemNumber, o.value('@Quantity','INT') AS Quantity,

o.value('@Price','MONEY') AS Price FROM @x.nodes('/Items/Item') x(o)

--

--- -- 5 Using XML parameters and return values

-- Typed and untyped XML parameters can be passed to a stored procedure -- as INPUT as well as OUTPUT parameters. XML parameters can be used as -- arguments as well as the return value of scalar functions or in result -- columns of table-valued functions. When a function returns an XML data

-- type value, XML data type methods can be directly called on the return value, -- as shown in the following example:

-- Create a function that returns an XML value CREATE FUNCTION GetOrderInfo(

@OrderID INT ) RETURNS XML AS

BEGIN

DECLARE @x XML SELECT @x = (

SELECT OrderID, CustomerID FROM OrderHeader

WHERE OrderID = @OrderID

FOR XML PATH(''),ROOT('OrderInfo')) RETURN @x

END GO

(6)

-- Call the function and invoke the value() method

SELECT dbo.GetOrderInfo(1).value('(OrderInfo/CustomerID)[1]','INT') AS CustomerID

--- -- 6 Loading/querying XML documents from disk files

-- The capability to load XML documents from disk files is one of the very -- interesting XML features available with SQL Server. This is achieved by -- using the BULK row set provider for OPENROWSET. The following example -- shows how to load the content of an XML file into an XML variable:

/*The sample code below assumes that a file named "items.xml"

exists in folder c:\temp with the following content.

<Items>

</Items>

*/

DECLARE @xml XML SELECT

@xml = CAST(bulkcolumn AS XML)

FROM OPENROWSET(BULK 'c:\temp\items.xml', SINGLE_BLOB) AS x SELECT

x.value('@ItemNumber','CHAR(4)') AS ItemNumber, x.value('@Quantity','INT') AS Quantity,

x.value('@Price','MONEY') AS Price FROM @xml.nodes('/Items/Item') i(x) -- OR

SELECT

x.value('@Price','MONEY') AS Price

(7)

FROM (

SELECT CAST(bulkcolumn AS XML) AS data

FROM OPENROWSET(BULK 'c:\temp\items.xml', SINGLE_BLOB) AS x ) a

CROSS APPLY data.nodes('/Items/Item') i(x)

--- -- 7 Limitations of the XML Data Type

DECLARE @EmployeeXML XML, @EmployeeText NVARCHAR(500) SELECT @EmployeeText = '

<EmployeeName>Jacob</EmployeeName>

<EmployeeName>Steve</EmployeeName>

</EmployeeInfo>'

SELECT DATALENGTH(@EmployeeText) AS StringSize SELECT @EmployeeXML = @EmployeeText

SELECT DATALENGTH(@EmployeeXML) AS XMLSize

--- -- 8 Understanding XML Data Type Methods

/*

XPath

<Items>

</Items>

*/

/*

value()

*/

DECLARE @x XML

(8)

SELECT @x= '<Order OrderID="1" OrderNumber="S0101" />' SELECT

@x.value('(Order/@OrderID)[1]','INT') AS OrderID,

@x.value('(Order/@OrderNumber)[1]','CHAR(5)') AS OrderNumber /*

nodes()

*/

DECLARE @x XML SELECT @x= '

<Items>

</Items>' SELECT

x.value('.','CHAR(4)') AS ItemNumber FROM @x.nodes('/Items/ItemNumber') o(x)

SELECT

OrderID,

x.value('@ItemNumber','CHAR(4)') AS ItemNumber FROM OrderXML

CROSS APPLY ItemData.nodes('/Order/Item') o(x)

/*

exist()

*/

SELECT

OrderID FROM OrderXML

WHERE ItemData.exist('/Order/Item[@ItemNumber = "Z001"]') = 1 --- -- 9 Joining XML Nodes with Relational Tables

(9)

SELECT

oh.OrderID,

c.Name AS Customer,

i.ItemDescription AS Item,

x.value('@Quantity','INT') AS Quantity, x.value('@Price','MONEY') AS Price FROM OrderHeader oh

INNER JOIN OrderXML ox ON

ItemData.value('(Order/@OrderID)[1]','INT') = oh.OrderID CROSS APPLY ItemData.nodes('/Order/Item') o(x)

INNER JOIN Customers c ON c.CustomerID = oh.CustomerID

INNER JOIN Items i ON i.ItemNumber = x.value('@ItemNumber','CHAR(4)') -- OR

SELECT

oh.OrderID,

c.Name AS Customer,

i.ItemDescription AS Item,

x.value('@Quantity','INT') AS Quantity, x.value('@Price','MONEY') AS Price FROM OrderHeader oh

INNER JOIN Customers c ON c.CustomerID = oh.CustomerID CROSS JOIN OrderXML ox

CROSS JOIN Items i

CROSS APPLY ItemData.nodes('

/Order[@OrderID=sql:column("oh.OrderID")]

/Item[@ItemNumber=sql:column("i.ItemNumber")]') o(x) --- -- 10 Using Variables an Filters in XQuery Expressions /* Illegal because it doesn't use a string literal DECLARE @node VARCHAR(100)

SELECT @node = ('/Order/Item') SELECT

(10)

*

FROM OrderXML

CROSS APPLY ItemData.nodes(@node) o(x)

*/

DECLARE @ItemNumber CHAR(4) SELECT @ItemNumber = 'D001' SELECT

x.value('@Price','MONEY') AS Price FROM OrderXML

CROSS APPLY ItemData.nodes('

/Order/Item[@ItemNumber=sql:variable("@ItemNumber")]

') o(x) --

DECLARE @Att VARCHAR(50) SELECT @Att = 'ItemNumber' SELECT

x.value('@*[local-name()=sql:variable("@Att")][1]','VARCHAR(50)') AS Value FROM OrderXML

--- -- 11 Access the Parent Node

-- This accesses the parent node through the parent node accessor '..' SELECT

x.value('../@OrderID','INT') AS OrderID,

-- This is apparently a more optimised way of doing though.

(11)

-- Using the CROSS APPLY, the query goes forward.

-- The accessor route is slower because it goes backwards.

SELECT

h.value('@OrderID','INT') AS OrderID,

CROSS APPLY ItemData.nodes('/Order') o(h) CROSS APPLY h.nodes('Item') i(x)

--- -- 12 Generating XML Output Using FOR XML AUTO

SELECT OrderNumber, CustomerID FROM OrderHeader

FOR XML AUTO /*

*/

SELECT OrderNumber, CustomerID FROM OrderHeader o

FOR XML AUTO /*

*/

FOR XML AUTO, ROOT('SalesOrder') /*

</SalesOrder>

(12)

*/

SELECT

[Order].OrderNumber, [Order].OrderDate,

Customer.CustomerNumber, Customer.Name

FROM OrderHeader [Order]

INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID FOR XML AUTO

/*

</Order>

</Order>

*/

SELECT

[Order].OrderNumber, [Order].OrderDate,

Customer.CustomerNumber, Customer.Name

INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID FOR XML AUTO, ELEMENTS

/*

<Order>

<Name>Jacob Sebastian</Name>

</Customer>

</Order>

<Order>

(13)

<Name>Jacob Sebastian</Name>

</Customer>

</Order>

*/

--- -- 13 Generating XML Output Using FOR XML RAW

FOR XML RAW('Order') /*

*/

SELECT OrderNumber, CustomerNumber FROM OrderHeader o

INNER JOIN Customers c ON o.CustomerID = c.CustomerID FOR XML RAW('Order')

/*

*/

FOR XML RAW('Order'), ROOT('Orders') /*

</Orders>

*/

(14)

--- -- 14 Generating XML Output Using FOR XML EXPLICIT

SELECT

1 AS Tag,

NULL AS Parent,

CustomerNumber AS 'Customer!1!CustNo', NULL AS 'LineItems!2!ItemNo',

NULL AS 'LineItems!2!Qty' FROM OrderHeader o

INNER JOIN Customers c ON o.CustomerID = c.CustomerID AND o.OrderID = 1

UNION ALL SELECT

2 AS Tag, 1 AS Parent, NULL,

i.ItemNumber, o.Quantity FROM Items i

INNER JOIN OrderDetails o ON i.ItemID = o.ItemID AND o.OrderID = 1

FOR XML EXPLICIT /*

</Customer>

OR as follows without FOR XML EXPLICIT

Tag Parent Customer!1!CustNo LineItems!2!ItemNo LineItems!2!Qty --- --- --- --- ---

1 NULL J001 NULL NULL

2 1 NULL D001 1

2 1 NULL Z001 1

(15)

*/

SELECT

1 AS Tag,

NULL AS Parent,

CustomerNumber AS 'Customer!1!CustNo!ELEMENT', NULL AS 'LineItems!2!ItemNo',

NULL AS 'LineItems!2!Qty' FROM OrderHeader o

INNER JOIN Customers c ON o.CustomerID = c.CustomerID AND o.OrderID = 1

UNION ALL SELECT

INNER JOIN OrderDetails o ON i.ItemID = o.ItemID AND o.OrderID = 1

FOR XML EXPLICIT /*

</Customer>

*/

SELECT

1 AS Tag,

NULL AS Parent,

CustomerNumber AS 'Order!1!CustNo', OrderNumber AS 'Order!1!OrderNo', NULL AS 'LineItems!2!ItemNo', NULL AS 'LineItems!2!Qty'

(16)

FROM OrderHeader o

INNER JOIN Customers c ON o.CustomerID = c.CustomerID UNION ALL

SELECT

NULL,

INNER JOIN OrderDetails o ON i.ItemID = o.ItemID FOR XML EXPLICIT, ROOT('Orders')

/*

</Order>

</Orders>

Tag Parent Order!1!CustNo Order!1!OrderNo LineItems!2!ItemNo LineItems!2!Qty --- --- --- --- --- ---

1 NULL J001 SO101 NULL NULL

1 NULL J001 SO102 NULL NULL

2 1 NULL NULL D001 1

2 1 NULL NULL Z001 1

2 1 NULL NULL D001 1

*/

SELECT

1 AS Tag,

NULL AS Parent,

CustomerNumber AS 'Order!1!CustNo', OrderNumber AS 'Order!1!OrderNo',

REPLACE(STR(OrderID,4) + STR(0,4),' ','0') AS 'Order!1!Sort!HIDE',

(17)

NULL AS 'LineItems!2!ItemNo', NULL AS 'LineItems!2!Qty' FROM OrderHeader o

INNER JOIN Customers c ON o.CustomerID = c.CustomerID UNION ALL

SELECT

NULL,

REPLACE(STR(OrderID,4) + STR(OrderDetailID,4),' ','0'), i.ItemNumber,

o.Quantity FROM Items i

INNER JOIN OrderDetails o ON i.ItemID = o.ItemID ORDER BY 'Order!1!Sort!HIDE'

FOR XML EXPLICIT, ROOT('Orders') /*

</Order>

</Orders>

Tag Parent CustNo OrderNo Order!1!Sort!HIDE LineItems!2!ItemNo LineItems!2!Qty --- --- --- --- --- --- --- 1 NULL J001 SO101 00010000 NULL NULL

2 1 NULL NULL 00010001 D001 1 2 1 NULL NULL 00010002 Z001 1 1 NULL J001 SO102 00020000 NULL NULL 2 1 NULL NULL 00020003 D001 1

*/

(18)

--- -- 15 Directives

SELECT

1 AS Tag,

NULL AS Parent,

'<Info about="XML"/>' AS 'MyData!1!!ELEMENT' FOR XML EXPLICIT

/*

*/

SELECT

1 AS Tag,

NULL AS Parent,

'<Info about="XML"/>' AS 'MyData!1!!XML' FOR XML EXPLICIT

/*

</MyData>

*/

SELECT

1 AS Tag,

NULL AS Parent,

'<Info about="XML"/>' AS 'MyData!1!!XMLTEXT' FOR XML EXPLICIT

/*

*/

SELECT

1 AS Tag,

NULL AS Parent,

'<Info about="XML"/>' AS 'MyData!1!!CDATA' FOR XML EXPLICIT

/*

(19)

*/

--- -- 16 Using the new simpler XML PATH

SELECT

1 AS Tag,

NULL AS Parent,

OrderNumber AS 'Order!1!OrderNumber', NULL AS 'Customer!2!CustomerNumber', NULL AS 'LineItems!3!',

NULL AS 'LineItems!4!ItemNo', NULL AS 'LineItems!4!Qty' FROM OrderHeader oh

WHERE OrderID = 2 UNION ALL

SELECT

c.CustomerNumber, NULL,

NULL, NULL

FROM OrderHeader oh

INNER JOIN Customers c ON oh.CustomerID = C.CustomerID AND OrderID = 2

UNION ALL SELECT

NULL, NULL, NULL, NULL UNION ALL

(20)

SELECT

NULL, NULL,

i.ItemNumber, od.Quantity FROM OrderDetails od

INNER JOIN Items i ON i.ItemID = od.ItemID AND od.OrderID = 2

FOR XML EXPLICIT, ROOT('Orders') /*

</LineItems>

</Order>

</Orders>

*/

SELECT

oh.OrderNumber AS '@OrderNumber',

c.CustomerNumber AS 'Customer/@CustomerNumber', i.ItemNumber AS 'LineItems/Item/@ItemNo', od.Quantity AS 'LineItems/Item/@Qty' FROM OrderHeader oh

INNER JOIN Customers c ON oh.CustomerID = c.CustomerID AND OrderID = 2

INNER JOIN OrderDetails od ON od.OrderID = oh.OrderID INNER JOIN Items i ON i.ItemID = od.ItemID

FOR XML PATH('Order'),ROOT('Orders') /*

(21)

</LineItems>

</Order>

</Orders>

*/

SELECT

CustomerID AS '@CustomerID', OrderNumber AS 'OrderNumber' FROM OrderHeader

FOR XML PATH('Order'), ROOT('Orders') /*

</Order>

</Order>

</Orders>

*/

SELECT

CustomerID AS '@CustomerID', OrderNumber AS '*'

FROM OrderHeader

FOR XML PATH('Order'), ROOT('Orders') /*

</Orders>

*/

SELECT

ItemNumber AS 'data()' FROM Items

(22)

FOR XML PATH(''), ROOT('Items') /*

*/

SELECT

ItemNumber AS 'text()' FROM Items

FOR XML PATH(''), ROOT('Items') /*

*/

SELECT

'Order Number' AS 'comment()', OrderNumber,

'Customer ID' AS 'comment()', CustomerID

FROM OrderHeader WHERE OrderID = 1 FOR XML PATH(''), ROOT('Orders') /*

</Orders>

*/

--- -- 17 The TYPE Directive

SELECT

SalesOrder.OrderNumber, SalesOrder.OrderDate, (SELECT CustomerNumber,

Name

FROM Customers Customer

(23)

FOR XML AUTO, TYPE), (SELECT ItemNumber,

Quantity, Price

FROM (SELECT i.ItemNumber, o.Quantity, o.Price FROM Items i

INNER JOIN OrderDetails o ON i.ItemID = o.ItemID WHERE OrderID = 1

) Item

FOR XML AUTO, ROOT('Items'),TYPE) FROM OrderHeader SalesOrder

WHERE OrderID = 1 FOR XML AUTO /*

<Items>

</Items>

</SalesOrder>

*/

SELECT (SELECT OrderID, CustomerID FROM OrderHeader

FOR XML AUTO, TYPE

).value('(OrderHeader/@OrderID)[1]','INT') AS OrderID

--- -- 18 The XSINIL Directive

SELECT OrderNumber, CustomerID,

NULL AS CustomerPhone FROM OrderHeader [Order]

FOR XML AUTO, ELEMENTS XSINIL

(24)

/*

</Order>

</Order>

*/

--- -- 19 Generating XML Schema Information

SELECT OrderNumber, CustomerID

FOR XML AUTO, XMLDATA /*

</ElementType>

</Schema>

*/

FOR XML AUTO, XMLSCHEMA

(25)

/*

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft- com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">

<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"

schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

<xsd:element name="Order">

<xsd:complexType>

<xsd:attribute name="OrderNumber">

<xsd:simpleType>

<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033"

sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

<xsd:maxLength value="20" />

</xsd:restriction>

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="CustomerID" type="sqltypes:int" />

</xsd:complexType>

</xsd:element>

</xsd:schema>

*/

FOR XML AUTO, XMLSCHEMA('urn:some-namespace') /*

<xsd:schema targetNamespace="urn:some-namespace" xmlns:schema="urn:some-namespace"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">

<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"

schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />

<xsd:element name="Order">

<xsd:complexType>

<xsd:attribute name="OrderNumber">

<xsd:simpleType>

(26)

<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033"

sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">

<xsd:maxLength value="20" />

</xsd:restriction>

</xsd:simpleType>

</xsd:attribute>

<xsd:attribute name="CustomerID" type="sqltypes:int" />

</xsd:complexType>

</xsd:element>

</xsd:schema>

*/

--- -- 20 Generating XML Namespaces

;WITH XMLNAMESPACES(

'http://www.sqlserverbible.com/orders' AS ord )

SELECT

CustomerID AS '@CustomerID', OrderNumber AS 'data()' FROM OrderHeader

</Orders>

*/

DEFAULT 'http://www.sqlserverbible.com/orders' )

SELECT

CustomerID AS '@CustomerID', OrderNumber AS 'data()'

(27)

FROM OrderHeader

</Orders>

*/

'http://www.sqlserverbible.com/customers' AS cust, 'http://www.sqlserverbible.com/orders' AS ord )

SELECT

OrderNumber AS 'ord:OrderNumber', CustomerID AS 'cust:CustomerID' FROM OrderHeader WHERE OrderID = 1 FOR XML PATH(''),ROOT('Orders') /*

<Orders xmlns:ord="http://www.sqlserverbible.com/orders"

xmlns:cust="http://www.sqlserverbible.com/customers">

<ord:OrderNumber>SO101</ord:OrderNumber>

<cust:CustomerID>1</cust:CustomerID>

</Orders>

*/

--- -- 21 Understanding XQuery and FLWOR Operations

SELECT ItemData.query('/Order/Item') FROM OrderXML

/*

*/

SELECT ItemData.query('/Order/Item[@ItemNumber="D001"]')

(28)

FROM OrderXML /*

*/

SELECT OrderID,

ItemData.query('

count(/Order/Item)

').value('.','INT') AS LineCount FROM OrderXML

--- -- 22 FLWOR Operation

DECLARE @x XML SELECT @x = ' <Items>

</Items>' SELECT

@x.query('

for $item in Items/ItemNumber return $item ')

/*

*/

DECLARE @x XML SELECT @x = ' <Items>

(29)

</Items>' SELECT

@x.query('

for $item in Items/ItemNumber where $item[. < "2000"]

order by $item return $item ') /*

*/

DECLARE @x XML SELECT @x = '

<Item ItemNumber="Z001" Quantity="1" Price="200" />' SELECT

@x.query('

for $item in Item return

{data($item/@ItemNumber)}

</ItemNumber> ') /*

*/

--- -- 23 What's New for XQuery in SQL Server 2008

(30)

<Item ItemNumber="Z001" Quantity="1" Price="200" />' SELECT

@x.query('

for $item in Item

let $itm := $item/@ItemNumber

let $tot := $item/@Quantity * $item/@Price return

<Item>

</Item>

') /*

<Item>

</Item>

<Item>

</Item>

*/

--- -- 24 Performing XML Data Modification

-- Insert DECLARE @x XML

SELECT @x = '<SalesOrder OrderNumber="S0101"/>' DECLARE @CustomerID INT

SELECT @CustomerID = 1 SET @x.modify('

insert element CustomerID {sql:variable("@CustomerID")}

as last into (SalesOrder)[1]

')

(31)

SELECT @x /*

</SalesOrder>

*/

-- Delete DECLARE @x XML SELECT @x = '

</SalesOrder>

'

DECLARE @CustomerID INT SELECT @CustomerID = 2 SET @x.modify('

delete (SalesOrder/CustomerID)[1]

') SELECT @x /*

*/

--- -- 25 What's New in XML DML in SQL Server 2008

DECLARE @doc XML, @val XML SELECT @doc = '

</SalesOrder>

'

(32)

SELECT @val = '

<Items>

</Items>

'

SET @doc.modify('

insert sql:variable("@val") as last into (SalesOrder)[1]

') SELECT @doc /*

<Items>

</Items>

</SalesOrder>

*/

--- -- 26 Handling Namespaces

<SalesOrder xmlns="http://www.sqlserverbible.com/order"

xmlns:cust="http://www.sqlserverbible.com/customer">

<cust:CustomerID>10001</cust:CustomerID>

</SalesOrder>

'

DEFAULT 'http://www.sqlserverbible.com/order',

'http://www.sqlserverbible.com/customer' AS cust )

SELECT

(33)

@x.value('(SalesOrder/OrderID)[1]','INT') AS OrderID,

@x.value('(SalesOrder/cust:CustomerID)[1]','INT') AS CustomerID ---

-- 27 Shredding XML USING OPENXML() DECLARE @hdoc INT

DECLARE @xml VARCHAR(MAX) SET @xml ='

<Items>

</Items>

</SalesOrder>

'

-- Step 1: Initialise XML Document Handle EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml -- Step 2: Call OPENXML

SELECT * FROM OPENXML(@hdoc, '/SalesOrder/Items/Item') WITH (

OrderNumber CHAR(5) '../../@OrderNumber', ItemNumber CHAR(4) '@ItemNumber',

Quantity INT '@Quantity', Price MONEY '@Price' )

-- Step 3: Free document handle exec sp_xml_removedocument @hdoc /*

OrderNumber ItemNumber Quantity Price --- --- --- ---

S0101 D001 1 900.00

S0101 Z001 1 200.00

*/

(34)

DECLARE @hdoc INT

DECLARE @xml VARCHAR(MAX) SET @xml ='

<itm:Items xmlns:itm="http://www.sqlserverbible.com/items">

<itm:Item ItemNumber="D001" Quantity="1" Price="900.0000" />

<itm:Item ItemNumber="Z001" Quantity="1" Price="200.0000" />

</itm:Items>

'

-- Step 1: Initialise XML Document Handle EXEC sp_xml_preparedocument

@hdoc OUTPUT, @xml,

'<itm:Items xmlns:itm="http://www.sqlserverbible.com/items"/>' -- Step 2: Call OPENXML

SELECT * FROM OPENXML(@hdoc, 'itm:Items/itm:Item') WITH (

ItemNumber CHAR(4) '@ItemNumber', Quantity INT '@Quantity',

Price MONEY '@Price' )

-- Step 3: Free document handle exec sp_xml_removedocument @hdoc /*

ItemNumber Quantity Price --- --- ---

D001 1 900.00

Z001 1 200.00

*/

--- -- 28 XSD and XML Schema Collections

-- Creating an XML Schema Collection

(35)

CREATE XML SCHEMA COLLECTION CustomerSchema AS '

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Customer">

<xs:complexType>

<xs:attribute name="CustomerID" use="required">

<xs:simpleType>

<xs:restriction base="xs:integer">

<xs:minInclusive value="1"/>

<xs:maxInclusive value="9999"/>

</xs:restriction>

</xs:simpleType>

</xs:attribute>

<xs:attribute name="CustomerName" use="optional">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="40"/>

</xs:restriction>

</xs:simpleType>

</xs:attribute>

</xs:complexType>

</xs:element>

</xs:schema>

'

-- Creating Typed XML Columns and Variables -- Create an XML Type Variable

DECLARE @x XML(CustomerSchema)

-- Create a Table with an XML Typed Column CREATE Table TypedXML(

ID INT,

CustomerData XML(CustomerSchema)) -- Alter a Table to Add an XML Typed Column

ALTER TABLE TypedXML ADD Customer2 XML(CustomerSchema) -- Performing Validation

DECLARE @x XML(CustomerSchema)

(36)

SELECT @x = '<Customer CustomerID="19909" CustomerName="Jacob"/>' -- Msg 6926, Level 16, State 1, Line 2

-- XML Validation: Invalid simple type value: '19909'. Location: /*:Customer[1]/@*:CustomerID -- XML Document

DECLARE @x XML(CONTENT CustomerSchema)

SELECT @x = '<Customer CustomerID="1001" CustomerName="Jacob"/>' -- XML Document

DECLARE @x XML(CONTENT CustomerSchema) SELECT @x = '

'

-- Adding Schemas to a Schema Collection -- Creating an XML Schema Collection

CREATE XML SCHEMA COLLECTION CustomerOrOrder AS '

<xs:element name="Customer">

<xs:complexType>

<xs:attribute name="CustomerID"/>

<xs:attribute name="CustomerName"/>

</xs:complexType>

</xs:element>

<xs:element name="Order">

<xs:complexType>

<xs:attribute name="OrderID"/>

<xs:attribute name="OrderNumber"/>

</xs:complexType>

</xs:element>

</xs:schema>

'

-- XML Document

DECLARE @x XML(CustomerOrOrder)

SELECT @x = '<Customer CustomerID="1001" CustomerName="Jacob"/>'

(37)

SELECT @x = '<Order OrderID="121" OrderNumber="10001"/>' SELECT @x = '

'

ALTER XML SCHEMA COLLECTION CustomerOrOrder ADD '

<xs:element name="Item">

<xs:complexType>

<xs:attribute name="ItemID"/>

<xs:attribute name="ItemNumber"/>

</xs:complexType>

</xs:element>

</xs:schema>

'

-- Tidy Up IF EXISTS(

SELECT name FROM sys.xml_schema_collections

WHERE schema_id = schema_id('dbo') AND name = 'CustomerOrOrder' ) DROP XML SCHEMA COLLECTION CustomerOrOrder

IF OBJECT_ID('TypedXML','U') IS NOT NULL DROP TABLE TypedXML GO

IF EXISTS(

SELECT name FROM sys.xml_schema_collections

WHERE schema_id = schema_id('dbo') AND name = 'CustomerSchema' ) DROP XML SCHEMA COLLECTION CustomerSchema