Accessing Data in SQL Server's XML Columns

SQL Server allows storing XML data in columns defined as XML data types. Retrieving specific data from these columns requires specialized queries.

Let's say you have an XML column named "Roles" with this structure:

AlphaBetaGamma

To find rows containing a particular role, use this query:

SELECT
  Roles
FROM
  MyTable
WHERE
  Roles.value('(/root/role)[1]', 'varchar(max)') LIKE 'StringToSearchFor'

This uses Roles.value to extract the first role element's value, converting it to varchar(max). This enables searching for specific roles.

If your column isn't already an XML data type, use CAST to convert it before querying.

The query can also target XML attributes. For example, with this XML in the "data" column:

To get rows where CodeSystem is "2":

SELECT
  [data]
FROM
  [dbo].[CodeSystemCodes_data]
WHERE
  CAST([data] AS XML).value('(/Utilities.CodeSystems.CodeSystemCodes/@CodeSystem)[1]', 'varchar(max)') = '2'

For more advanced XML querying in T-SQL, see:

• Querying XML fields using t-sql
• Flattening XML Data in SQL Server

Alternatively, CROSS APPLY offers more flexibility for searching multiple "role" elements:

SELECT *
FROM
(
    SELECT
        pref.value('(text())[1]', 'varchar(32)') AS RoleName
    FROM
        MyTable
    CROSS APPLY
        Roles.nodes('/root/role') AS Roles(pref)
) AS Result
WHERE
    RoleName LIKE '%ga%'

This approach allows for more efficient and targeted extraction of data from XML columns in SQL Server.