Saturday, July 3, 2010

Cross Apply in XML

Query :


DECLARE @XML XML =
'<Customers>;
<Customer ID="1">;
<Address1>;Address - 11</Address1>;
<Address2>;Address - 12</Address2>;
</Customer>;
<Customer ID="2">;
<Address1>;Address - 21</Address1>;
<Address2>;Address - 22</Address2>;
</Customer>;
</Customers>;'


SELECT
Customer.value('@ID', 'INT') AS CustomerID
, CustAdd1.value('.', 'VARCHAR(25)') Address1
, CustAdd2.value('.', 'VARCHAR(25)') Address2
FROM @XML.nodes('/Customers/Customer')a(Customer)
CROSS APPLY Customer.nodes('Address1')aa(CustAdd1)
CROSS APPLY Customer.nodes('Address2')bb(CustAdd2)


Output :
----------- ------------------------- -------------------------
CustomerID Address1 Address2
----------- ------------------------- -------------------------
1 Address - 11 Address - 12
2 Address - 21 Address - 22
----------- ------------------------- -------------------------

No comments: