Reading the value from xml variable

• Suvasish Das

  Grasshopper

  Points: 17

  More actions

  October 12, 2009 at 4:10 am

  #215435

  Dear sir,

  I am facing problem while using xquery in sql server 2005.

  i am mentioing the xml variable and the required output below.

  DECLARE @productIds xml

  SET @productIds ='

  <Products>

  <Desc>

  <Name>Suvasish</Name>

  </Desc>

  <Desc>

  <Name>Debasish</Name>

  </Desc>

  <Details>

  <MfgDate>Test1</MfgDate>

  <Content>1</Content>

  <Content>2</Content>

  </Details>

  <Details>

  <MfgDate>Test2</MfgDate>

  <Content>3</Content>

  <Content>4</Content>

  </Details>

  </Products>'

  Output required:

  Suvasish1Test1

  Suvasish2Test1

  Suvasish3Test2

  Suvasish4Test2

  Debasish1Test1

  Debasish2Test1

  Debasish3Test2

  Debasish4Test2

• Mark Cowne

  One Orange Chip

  Points: 26983

  More actions

  October 12, 2009 at 4:49 am

  #1064650

  SELECT x1.value('.','VARCHAR(20)'),

  x3.value('.','VARCHAR(20)'),

  x2.value('.','VARCHAR(20)')

  FROM @productIds.nodes('/Products/Desc/Name') AS n1(x1)

  CROSS JOIN @productIds.nodes('/Products/Details/MfgDate') AS n2(x2)

  CROSS APPLY x2.nodes('../Content') AS n3(x3)

  ____________________________________________________

  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