|
CREATE TABLE customerData(
ID int IDENTITY(1,1) NOT NULL,
customerDocs xml NOT NULL,
updated datetime NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_customerData] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO customerData(customerDocs)
VALUES(N'<?xml version="1.0"?>
<customers>
<customer FirstName="Bob" LastName="Hayes" Zipcode="91126" status="current">
<order ID="12221" Date="July 1, 2006">Laptop</order>
</customer>
<customer FirstName="Judy" LastName="Amelia" Zipcode="23235" status="current">
<order ID="12221" Date="April 6, 2006">Workstation</order>
</customer>
<customer FirstName="Howard" LastName="Golf" Zipcode="20009" status="past due">
<order ID="3331122" Date="December 8, 2005">Laptop</order>
</customer>
<customer FirstName="Mary" LastName="Smith" Zipcode="12345" status="current">
<order ID="555555" Date="February 22, 2007">Server</order>
</customer>
</customers>')
GO
-- FLWOR with LET operator
SELECT customerDocs.query('
<CustomerOrders> {
for $i in //customer
let $name := concat($i/@FirstName, " ", $i/@LastName)
order by $i/@LastName
return
<Customer Name="{$name}">
{
$i/order
}
</Customer>
}
</CustomerOrders>
')
FROM customerData
|