Shows how to extract multiple values from a column in T-SQL.

tsql_multiple_values_in_column.sql

;WITH cte(value, id) AS (
  SELECT *
    FROM (
         VALUES
          ('HERP DREP CHS XMS', 1)
         ,('HELLO WORLD', 2)
       ) AS x(a, b)
)
SELECT 
  flags.flag.value('.', 'varchar(50)') AS Flag
 ,id AS "Order ID"
 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS "KEY"
FROM (
  SELECT
     CAST('<flag>' + REPLACE(value, ' ', '</flag><flag>') + '</flag>' AS XML)
    ,id
  FROM cte
) AS f(x, id)
CROSS APPLY f.x.nodes('flag') AS flags(flag)

See my blog post for more details: http://comp-phil.blogspot.com/2013/11/working-with-multiple-values-in-column.html