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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi