Extracting XML data in SQL - too many cross apply statements

[bloggi]

I have an xml document containing details from a Statement:\[code\]<Statement>*Id, Date, Address etc*<Invoices> <Invoice> *Id, Date, AmountDue etc* <Products> <Product> *ProdId, Price, etc* </Product> <Product> *ProdId, Price, etc* </Product> </Products> </Invoice> <Invoice> *Id, Date, AmountDue etc* <Products> <Product> *ProdId, Price, etc* </Product> <Product> *ProdId, Price, etc* </Product> </Products> </Invoice> <Invoice> *Id, Date, AmountDue etc* <Products> <Product> *ProdId, Price, etc* </Product> <Product> *ProdId, Price, etc* </Product> </Products> </Invoice></Invoices><Payments> <Payment> *Id, Date, Amount etc* </Payment> <Payment> *Id, Date, Amount etc* </Payment> <Payment> *Id, Date, Amount etc* </Payment></Payments></Statement>\[/code\]This works fine for the Statement specific details:\[code\]SET @statementId = @xml.value('(Id)[1]', 'UNIQUEIDENTIFIER');\[/code\]but it requires a singleton, and only returns the first value. The statement specific values are all singletons, so this is fine.I am able to get the information out using cross apply statements like this:\[code\]SELECT Id.value('.', 'uniqueidentifier') AS INVOICE_IDFROM @xml.nodes('Statement') A(S)cross apply S.nodes('Invoices/Invoice') B(InvoiceD)cross apply InvoiceD.nodes('Id') C(Id)\[/code\]This returns an Id from each Invoice in the Statement - perfect.My problem comes when I try to extract all of the invoice details AND Product details AND Payment details. I currently have seven cross apply statements and I get the message:"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."What I want to do is have one cross apply for:InvoiceProductPaymentand narrow down the exact field in the select statement, but unless I use '.' I must make the statement return a singleton and I don't get all of the data.I have done some research about specifying a namespace within the select statement, but all of the examples set the namespace to be an http address instead of a node in an xml document and I haven't gotten anything to return yet using this approach.Where should I go from here?