gardnerjam
New Member
So I have the below that will allow me to get xml from one record in the \[code\]Products\[/code\] table.However, I need to to be able to get the XML from ALL records in the \[code\]Products\[/code\] table, along with its \[code\]ProductId\[/code\].I'm confused on how to start this.\[code\]DECLARE @MyXML XMLSET @MyXML = (SELECT ProductsXML FROM Products WHERE ProductId= 1)SELECT a.b.value('@upccode','int') as UPC, a.b.value('@dateadded','date') as DateAddedFROM @MyXML.nodes('xml/Product/UPC')a(b);\[/code\]I'm using SQL Server 2008.Test Data:\[code\]ProductId: 1ProductsXML:<xml> <Product> <UPC upccode="1237" dateadded="10/9/2012"/> <UPC upccode="1236" dateadded="10/8/2012"/> <UPC upccode="1235" dateadded="10/7/2012"/> <UPC upccode="1234" dateadded="10/6/2012"/> </Product> </xml>ProductId: 2ProductsXML:<xml> <Product> <UPC upccode="9876" dateadded="9/9/2012"/> <UPC upccode="9877" dateadded="9/8/2012"/> <UPC upccode="0998" dateadded="9/7/2012"/> <UPC upccode="7877" dateadded="9/6/2012"/> </Product> </xml>\[/code\]The Result I'm looking for is something like this:\[code\]ProductId UPC DateAdded--------- --- ---------1 1237 10/9/20121 1236 10/8/2012....2 9876 9/9/20122 9877 9/8/2012\[/code\]Right now I can get the above but ONLY by specifying one \[code\]PoductId\[/code\] at a time. I want to be able to run all of the `Products' without specifying each entry.