IS XMLNAMESPACES and CTE's supported
PaulSteward
Posts: 2
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 ;
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
Thanks for posting. I have noted your suggestion for inclusion in a future version of SQL Refactor.