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