Querying XML Column Values in SQL Server Part II

--Querying XML datatype using T-SQL

In the previous example we read XML Tag values and this example it shows how to read values of individual tags.

DECLARE @XMLTest TABLE (xmlString XML)

INSERT INTO @XMLTest VALUES ('
<SELECT>
<FIELD Display="InvoiceNumber">SalesData.InvoiceNumber</FIELD>
<FIELD Display="InvoiceDate">SalesData.InvoiceDate</FIELD>
<FIELD Display="InvoiceDescription">SalesData.InvoiceDescription</FIELD>
<FIELD Display="OrderedDate">SalesData.OrderedDate</FIELD>
<FIELD Display="OrderSummary">SalesData.OrderSummary</FIELD>
<FIELD Display="OrderDeliveryDate">SalesData.OrderDeliveryDate</FIELD>
<FIELD Display="OrderQuantity">SalesData.OrderQuantity</FIELD>
<FIELD Display="OrderAmount">SalesData.OrderAmount</FIELD>
<FROM>SalesData</FROM>
<ORDERBY>Invoice Number ASC</ORDERBY>
</SELECT>')

SELECT x.value('@Display[1]', 'VARCHAR(20)') AS Name
FROM @XMLTest t CROSS APPLY XMLSTRING.nodes('/SELECT/FIELD') a(x)