Hi there,
I have an irritating problem which I just cant seem to solve. I am trying to retrieve the relational dataset from an xml column using the nodes method provided by SQL Server 2005. This shredding to relational data is done by one stored procedure which is triggered when the xml document is retrieved by a soap service. The stored procedure first inserts the xmldocument into an xml column after which its start shredding the data to relational data.
The error I receive is:
Msg 8623, Level 16, State 1, Line 139
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.
Below is a short code snippet of the stored procedure. This snippit only represents one sction. In total the stored procedure contains three more sections like the one displayed here.
PRINT 'STAP2A: Update Home Data'
UPDATE tb_Import_WoningGegevensRelationeel
SET
cv_aanwezig = q.cv_aanwezig,
stadsverwarming = q.stadsverwarming,
cv_combi = q.cv_combi,
warmtemeter = q.warmtemeter,
oppberging = q.oppberging,
verhuurdatum = q.verhuurdatum,
garage_carport = q.garage_carport,
bouwjaar = q.bouwjaar,
lift_aanwezig = q.lift_aanwezig,
codebuitenruimte = q.codebuitenruimte,
marktkenmerk = q.marktkenmerk,
gebruik = q.gebruik
FROM
(
SELECT
VHE.C.value('(./vhe)[1]','nvarchar(max)') As VheNummerUitgebreid,
cv_aanwezig.C.value('(.)[1]','nvarchar(max)') cv_aanwezig,
stadsverwarming.C.value('(.)[1]','nvarchar(max)') stadsverwarming,
cv_combi.C.value('(.)[1]','nvarchar(max)') cv_combi,
warmtemeter.C.value('(.)[1]','nvarchar(max)') warmtemeter,
oppberging.C.value('(.)[1]','nvarchar(max)') oppberging,
verhuurdatum.C.value('(.)[1]','nvarchar(max)') verhuurdatum,
garage_carport.C.value('(.)[1]','nvarchar(max)') garage_carport,
bouwjaar.C.value('(.)[1]','nvarchar(max)') bouwjaar,
lift_aanwezig.C.value('(.)[1]','nvarchar(max)') lift_aanwezig,
codebuitenruimte.C.value('(.)[1]','nvarchar(max)') codebuitenruimte,
marktkenmerk.C.value('(.)[1]','nvarchar(max)') marktkenmerk,
gebruik.C.value('(.)[1]','nvarchar(max)') gebruik
FROM @vheupdate.nodes('//row') VHE(C)
OUTER APPLY VHE.C.nodes('./cv_aanwezig') cv_aanwezig(C)
OUTER APPLY VHE.C.nodes('./stadsverwarming') stadsverwarming(C)
OUTER APPLY VHE.C.nodes('./cv_combi') cv_combi(C)
OUTER APPLY VHE.C.nodes('./warmtemeter') warmtemeter(C)
OUTER APPLY VHE.C.nodes('./oppberging') oppberging(C)
OUTER APPLY VHE.C.nodes('./verhuurdatum') verhuurdatum(C)
OUTER APPLY VHE.C.nodes('./garage_carport') garage_carport(C)
OUTER APPLY VHE.C.nodes('./bouwjaar') bouwjaar(C)
OUTER APPLY VHE.C.nodes('./lift_aanwezig') lift_aanwezig(C)
OUTER APPLY VHE.C.nodes('./codebuitenruimte') codebuitenruimte(C)
OUTER APPLY VHE.C.nodes('./marktkenmerk') marktkenmerk(C)
OUTER APPLY VHE.C.nodes('./gebruik') gebruik(C)
)q
WHERE vhe = q.VheNummerUitgebreid AND WoningXML_id = @WoningXML_id
When I execute each section individually there is no problem. But when all four sections are run sequentially, by executing the stored procedure, SQL Server 2005 just says no 