CODEWITHSUNDEEP
sqlxmlsql-server-2012
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

Why do I see a incorrect syntax error

I have the following SQL query which takes multiple XML nodes and displays in a table:

SELECT
    PhysicianName = XC.value('(name)[1]', 'varchar(50)'),
    Gender = XC.value('(gender)[1]', 'varchar(50)'),
    LangSpoken = XLang.value('.', 'varchar(20)'),
    InsAccepted = XIns.value('.', 'varchar(50)')
FROM
    [MYDB].[dbo].[content]
CROSS APPLY
    CAST([content_html] AS XML).nodes('/root/Physicians') AS XT(XC)
CROSS APPLY
    XC.nodes('langAccept') AS XT2(XLang)
CROSS APPLY
    XC.nodes('insAccept') AS XT3(XIns)

I have to CAST the content_html as XML because it is ntext format. I am receiving the following error:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'

How can I resolve the error

Upvotes: 1

Views: 798

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

1) On short term, you could replace 

FROM
    [MYDB].[dbo].[content]
CROSS APPLY
    CAST([content_html] AS XML).nodes('/root/Physicians') AS XT(XC)

with

FROM ( 
    SELECT CAST(content_html AS XML) AS content_xml 
    FROM [MYDB].[dbo].[content] AS c
) AS x
CROSS APPLY x.content_xml.nodes('/root/Physicians') AS XT(XC)

2) Or you could change the data type of column content_html

How ?

CREATE TABLE dbo.MyTable (content_html NTEXT)
INSERT dbo.MyTable VALUES (N'<a>text</a>')
GO
ALTER TABLE dbo.MyTable ADD content_xml XML
GO
UPDATE dbo.MyTable SET content_xml = CONVERT(XML, content_html)
-- ALTER TABLE dbo.MyTable ADD content_xml XML NOT NULL -- Is mandatory column ?
GO
ALTER TABLE dbo.MyTable DROP COLUMN content_html;
EXEC sp_rename 'dbo.MyTable.content_xml', 'content_html', 'COLUMN'
GO
SELECT * FROM dbo.MyTable

or else

3) you could add a persisted computed column thus

ALTER TABLE dbo.content
ADD content_xml AS ( CAST(content_html AS XML) ) PERSISTED

In this last case, the cons is the extra storage space needed to store the same data as XML.

4) Warning: if the same physician has 2 or more LangSpoken and also two or more InsAccepted then the resultset will contain for every person a cross join of these values.

Upvotes: 3

Related Questions