XML Column Read

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  February 4, 2013 at 8:23 am

  #268118

  Hi,

  I have a column in table with xml type. I want to extract it row wise but it is giving me only first result

  My Table has following data

  id ItemId

  1 <itemids><itemid>259</itemid><itemid>489</itemid></itemids>

  2 <itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>

  3 <itemids><itemid>6603537</itemid></itemids>

  select

  tabId,

  itemid.value('(/itemids//itemid/node())[1]','int') AS ItemIDA

  from mytable;

  query is only returning first ItemId against every Id.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  February 4, 2013 at 8:38 am

  #1583995

  Based on your sample data: -

  IF object_id('tempdb..#yourTable') IS NOT NULL

  BEGIN

  DROP TABLE #yourTable;

  END;

  SELECT id, CAST(ItemId AS XML) AS ItemId

  INTO #yourTable

  FROM (VALUES(1, '<itemids><itemid>259</itemid><itemid>489</itemid></itemids>'),

  (2, '<itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>'),

  (3, '<itemids><itemid>6603537</itemid></itemids>')

  )a(id, ItemId);

  You'd do it like this: -

  SELECT id, ParamValues.ItemId.value('.','INT') AS ItemId

  FROM #yourTable

  OUTER APPLY ItemId.nodes('/itemids/itemid') ParamValues(ItemId);

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• thbaig

  Hall of Fame

  Points: 3006

  More actions

  February 4, 2013 at 11:15 am

  #1584052

  thank you. 🙂

  this is what I have implemented