XML in SQL Server

• MB-1081964

  Newbie

  Points: 7

  More actions

  October 13, 2012 at 2:14 am

  #393382

  I have a table like below.

  valuexml_1

  1+2<root><s>1</s><s>2</s></root>

  3+4<root><s>3</s><s>4</s></root>

  I want to get the value like,

  1+2 1

  1+2 2

  3+4 3

  3+4 4

  Can you guys please help me urgently?

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  October 15, 2012 at 1:48 am

  #1549010

  DECLARE @t TABLE(value VARCHAR(10),xml_1 XML)

  INSERT INTO @T(value,xml_1)

  VALUES('1+2','<root><s>1</s><s>2</s></root>'),

  ('3+4','<root><s>3</s><s>4</s></root>')

  SELECT value,x.r.value('.','INT') AS s

  FROM @t

  CROSS APPLY xml_1.nodes('/root/s') AS x(r)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537