XML data type is a powerful feature introduced by SQL Server 2005 that allows storing and manipulating XML data within the database. In this article, we will dive deeper into XML processing using the Value() method.

Example 1: Retrieving Attribute Values

The Value() method can be used to retrieve attribute values from XML variables. By prefixing the attribute names with “@”, we can extract specific attribute values. Here’s an example:

DECLARE @x XML
SET @x = '
<orderInfo>
  <item code="A001" category="FOOD" subcategory="Candies">
    <description>Nestle Munch</description>
    <qty>10</qty>
    <rate>11.25</rate>
  </item>
  <item code="A002" category="FOOD" subcategory="Biscuits">
    <description>Britania Good Day</description>
    <qty>15</qty>
    <rate>12.25</rate>
  </item>
</orderInfo>'

SELECT
  x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
  x.item.value('@category[1]', 'VARCHAR(20)') AS category,
  x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory
FROM @x.nodes('//orderInfo/item') AS x(item)

The above query will retrieve the attribute values from the XML variable and display them as columns.

Example 2: Retrieving Node Values

If we want to retrieve values from XML nodes instead of attributes, we can simply omit the “@” sign. Here’s an example:

DECLARE @x XML
SET @x = '
<orderInfo>
  <item code="A001" category="FOOD" subcategory="Candies">
    <description>Nestle Munch</description>
    <qty>10</qty>
    <rate>11.25</rate>
  </item>
  <item code="A002" category="FOOD" subcategory="Biscuits">
    <description>Britania Good Day</description>
    <qty>15</qty>
    <rate>12.25</rate>
  </item>
</orderInfo>'

SELECT
  x.item.value('description[1]', 'VARCHAR(20)') AS description,
  x.item.value('qty[1]', 'INT') AS qty,
  x.item.value('rate[1]', 'FLOAT') AS rate
FROM @x.nodes('//orderInfo/item') AS x(item)

This query retrieves the values of the specified nodes from the XML variable.

Example 3: Retrieving Both Attributes and Node Values

We can also retrieve both attribute values and node values in a single query. Here’s an example:

DECLARE @x XML
SET @x = '
<orderInfo>
  <item code="A001" category="FOOD" subcategory="Candies">
    <description>Nestle Munch</description>
    <qty>10</qty>
    <rate>11.25</rate>
  </item>
  <item code="A002" category="FOOD" subcategory="Biscuits">
    <description>Britania Good Day</description>
    <qty>15</qty>
    <rate>12.25</rate>
  </item>
</orderInfo>'

SELECT
  x.item.value('@code[1]', 'VARCHAR(20)') AS ItemCode,
  x.item.value('@category[1]', 'VARCHAR(20)') AS category,
  x.item.value('@subcategory[1]', 'VARCHAR(20)') AS subcategory,
  x.item.value('description[1]', 'VARCHAR(20)') AS description,
  x.item.value('qty[1]', 'INT') AS qty,
  x.item.value('rate[1]', 'FLOAT') AS rate
FROM @x.nodes('//orderInfo/item') AS x(item)

This query retrieves both attribute values and node values from the XML variable.

Conclusion

In this article, we explored the Value() method exposed by the XML data type in SQL Server. We learned how to retrieve attribute values and node values using this method. The Value() method provides a powerful way to process XML data within the database. In future articles, we will cover other methods for XML processing.