Post reply

Xquery Question

  • 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

    (

    ProductID int primary key,

    CatalogType varchar(50),

    CatalogDescription xml

    )

    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

  • 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

  • December 11, 2009 at 7:45 am

    #1091193

    Works great!

    Thanks Mark!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply