Competition: What’s your favorite Redgate tool? Enter now.

IS XMLNAMESPACES and CTE's supported

PaulStewardPaulSteward Posts: 2
edited April 17, 2007 10:14AM in SQL Refactor Previous Versions
SQL Refactor considers the following syntax invalid. XMLNAMESPACES together with common table expression are legitimate syntax.

For example:

DECLARE @doc XML
SET @doc=
N'
<doc>
<Item Id="1IA12FLOJ11EVA9KHLT" Type="Wap" DisplayText="Help"/>
</doc>
';

WITH XMLNAMESPACES ( DEFAULT 'http://tempuri.org/XMLSchema.xsd' ),
Items
AS ( SELECT tab.col.value('@Id', 'nvarchar(50)') AS ID,
tab.col.value('@Type', 'nvarchar(20)') AS Type,
tab.col.value('@DisplayText', 'nvarchar(100)') AS DisplayText,
tab.col.value('./Url[1]', 'varchar(255)') AS URI
FROM (select @doc as Doc) as t
CROSS APPLY Doc.nodes('//Item') AS tab ( col )
)
SELECT DISTINCT
Type
FROM Items
WHERE URI IS NULL ;

Comments

Sign In or Register to comment.