Querying XML Column Values in SQL Server Part I

--Querying XML datatype using T-SQL

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 xmlString FROM @XMLTest

SELECT
    XMLSTRING.value('(/SELECT/FROM)[1]', 'CHAR(200)') FROM_VALUE,
    XMLSTRING.value('(/SELECT/ORDERBY)[1]', 'CHAR(200)') ORDERBY_VALUE
FROM @XMLTest

/SELECT/FROM = (RootNode / SubNode)
[1]  = Indicates field Number


SELECT
    XMLSTRING.value('(/SELECT/FROM)[1]', 'CHAR(200)') FROM_VALUE,
    XMLSTRING.value('(/SELECT/ORDERBY)[1]', 'CHAR(200)') ORDERBY_VALUE,
    XMLSTRING.value('(/SELECT/FIELD)[1]', 'CHAR(200)') FIELD_VALUE1,
    XMLSTRING.value('(/SELECT/FIELD)[2]', 'CHAR(200)') FIELD_VALUE2
FROM @XMLTest