Xml Excercise 3, Sql Server

Xml Excercise 3

DECLARE @DocHandle AS INT;
DECLARE @XmlDocument AS xml;
SET @XmlDocument = N'
<CustomersOrders>
<Customer custid="1">
<companyname>Customer NRZBB</companyname>
<employeename>Anand Somaiya</employeename>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2">
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10926">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

-- Create an internal representation
EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;

-- Attribute-centric mapping
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',1)
WITH (custid INT,
companyname NVARCHAR(40));

-- Element-centric mapping
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',2)
WITH (custid INT,
companyname NVARCHAR(40));

-- Attribute- and element-centric mapping
-- Combining flag 8 with flags 1 and 2
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',11)
WITH (custid INT,
companyname NVARCHAR(40),employeename varchar(40));

-- Remove the DOM
EXEC sys.sp_xml_removedocument @DocHandle;

select @XmlDocument.query('*');
select @XmlDocument.query('data(*)')
select @XmlDocument.query('data(CustomersOrders/Customer/companyname)')

DECLARE @x AS XML = N'
<Employee empid="2">
<FirstName>fname</FirstName>
<LastName>lname</LastName>
</Employee>
<Employee empid="3">
<FirstName>fname3</FirstName>
<LastName>lname3</LastName>
</Employee>
';
DECLARE @v AS NVARCHAR(20) = N'FirstName';
SELECT @x.query('
if (sql:variable("@v")="FirstName") then
(/Employee/FirstName)
else
/Employee/LastName
') AS FirstOrLastName;

SELECT @XmlDocument.query('CustomersOrders/Customer/Order');

SET @XmlDocument.modify('replace value of
/CustomersOrders[1]/Customer[1]/companyname[1]/text()[1]
with "New Company Name"');
SELECT @XmlDocument.value('(/CustomersOrders/Customer/companyname)[1]',
'NVARCHAR(20)')
AS [First Customer New Name];


run \| edit \| history \| help	0