xml-like string to individual rows

• Mindy Hreczuck

  SSCommitted

  Points: 1796

  More actions

  October 12, 2011 at 11:46 am

  #249870

  I have columnA with a single value and columnB with strings in it such as:

  <ITEM>Jane, Mary</ITEM> <ITEM> Smith, Joe</ITEM> <ITEM>Doe, Jane</ITEM>

  I've been trying to parse it out with patindex or charindex to substring out each name so I can insert them into a reporting table, eg. substring(columnA,@start,@end). But I am having a difficult time figuring out how to move @start and @end around the tags. I can't seem to get past breaking out the first two names. Is there a better way to do this?

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 12, 2011 at 1:16 pm

  #1393670

  Here's a solution using XQuery to parse the (obviously) xml data. Since I don'T know the data type of the related column, I used a cte to convert it into an xml column.

  Also please note how I posted the sample data in a ready to use format. That makes it a lot easier for those of us trying to help you...

  DECLARE @tbl TABLE (id INT, val VARCHAR(300))

  INSERT INTO @tbl

  SELECT 1,'<ITEM>Jane, Mary</ITEM> <ITEM> Smith, Joe</ITEM> <ITEM>Doe, Jane</ITEM>'

  ;

  WITH cte AS

  (

  SELECT

  id, CAST(val AS XML) AS x

  FROM @tbl

  )

  SELECT id,

  c.value('.','varchar(30)') AS each_name

  FROM cte

  CROSS APPLY x.nodes('/ITEM') T(c)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Mindy Hreczuck

  SSCommitted

  Points: 1796

  More actions

  October 13, 2011 at 8:20 am

  #1394070

  Thanks so much! There's a lot in there to wrap my head around, I'm still learning the xml manipulations. I hadn't provided any further info as it didn't matter to the problem. I didn't need to do any tweaking to your example for it to work, perfect. Thanks again.