Sunday, February 19, 2012

CTE and XQuery

I'ev been playing around with CTE's and trying to query a table that has the following structure

tbl Projects{
id (int),
Project (xml)
}

sample data within the project column is
id = 100
Project =
'<Projects>
<SubProjects>
<id>150</id>
<id>160</id>
<id>170</id>
</SubProjects>
</Projects>'

basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query ..

WITH MySubProjects(SubProjects)
AS
(
SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects
FROM RB_Projects TblProjects
CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs)
where id = 100
)

but i keep getting the following error
Incorrect syntax near ')'.

does CTE's not like xquery functions within it's expression?n/m

guess i gotta use the CTE after definine it...

"A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE

statement that
references some or all the CTE columns."
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

No comments:

Post a Comment