Paul White (2/21/2010)

---

GSquared (2/19/2010)

---

You'll need to use your column name instead of the XML variable, but it should do what you want.

A much more efficient query plan is produced from this code:

DECLARE @XML XML;

SET @XML =

N'

<ROOT>

<Customers>

<CustomerId>1111</CustomerId>

<CompanyName>Sean Chai</CompanyName>

<City>NY</City>

</Customers>

<Customers>

<CustomerId>1112</CustomerId>

<CompanyName>Tom Johnston</CompanyName>

<City>LA</City>

</Customers>

<Customers>

<CustomerId>1113</CustomerId>

<CompanyName>Institute of Art</CompanyName>

</Customers>

</ROOT>';

SELECT DV.customer_id,

DV.company_name

FROM @XML.nodes('./ROOT/Customers')

AS T(customers)

CROSS

APPLY (

SELECT customers.value('(CustomerId/text())[1]', 'VARCHAR(100)'),

customers.value('(CompanyName/text())[1]', 'VARCHAR(100)')

)

AS DV (customer_id, company_name);

Paul