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)

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

 

Querying XML datatype using T-SQL

--- Extract Strings from XML data type using T-SQL ---

---- Fetch value in XML Node format ----

DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <name>Kalyan Kumar</name>
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
</Employee>'
SELECT @xmlstring.query('(/Employee/name)')  AS Emp_Name
SELECT @xmlstring.query('/Employee/name/text()')  AS Emp_Name

---- Fetch value in String format ----
DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <name>Kalyan Kumar</name>
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
</Employee>'
SELECT @xmlstring.value('(/Employee/name)[1]', 'CHAR(30)')  AS Emp_Name

---- Fetch value from XML Sub Node in string format----
DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <Details name="Kalyan Kumar">
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
  </Details>
  </Employee>'
SELECT @xmlstring.value('(//Details/@name)[1]','char(30)')
SELECT @xmlstring.value('(/Employee/Details/city)[1]', 'CHAR(30)')