Xquery Question

• Mister Ken

  SSChasing Mays

  Points: 659

  More actions

  December 10, 2009 at 5:17 pm

  #137629

  How can I have the select statement return both products (10 and 20) in the select statement as two separate rows?

  -- Create the table.

  CREATE TABLE T

  (

  ProductIDint primary key,

  CatalogTypevarchar(50),

  CatalogDescriptionxml

  )

  GO

  -- Insert sample data.

  INSERT INTO T values(1,'Product','<ProductDescription ProductID="10" ProductName="SomeName" /><ProductDescription ProductID="20" ProductName="SomeName2" />')

  GO

  SELECT

  CatalogDescription.value('(/ProductDescription/@ProductID)[1]', 'int') AS PID,

  CatalogDescription.value('(/ProductDescription/@ProductName)[1]', 'varchar(40)') AS PName

  FROM T

  WHERE

  CatalogType = 'Product'

  GO

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  December 11, 2009 at 2:14 am

  #1091029

  SELECT r.value('@ProductID', 'int') AS PID,

  r.value('@ProductName', 'varchar(40)') AS PName

  FROM T

  CROSS APPLY CatalogDescription.nodes('/ProductDescription') 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

• Mister Ken

  SSChasing Mays

  Points: 659

  More actions

  December 11, 2009 at 7:45 am

  #1091193

  Works great!

  Thanks Mark!