Showing posts with label structuretbl. Show all posts
Showing posts with label structuretbl. Show all posts

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