Link to home
Start Free TrialLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Parsing Problem semi colon expected

I have the following query as below

IF OBJECT_ID('tempdb.dbo.#xml_temp') IS NOT NULL
    DROP TABLE #xml_temp
SET NOCOUNT ON

CREATE TABLE #xml_temp

etc etc

SET NOCOUNT OFF

DROP TABLE #xml_temp

This query worked fine until recently, I think the server was upgraded from 2005 to 2008

I now get the error message XML Parsing line 1, ccharacter 25, semi colon expected

Can anybody help?

Avatar of chapmandew
chapmandew
Flag of United States of America image

post all of your code.
ASKER CERTIFIED SOLUTION
Avatar of dodge20
dodge20

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think SQL Server 2008 now allows Temp tables (# table), you need to use temp table variable .
Avatar of Mark Wilson
Mark Wilson

ASKER

code as follows

IF OBJECT_ID('tempdb.dbo.#xml_temp') IS NOT NULL
    DROP TABLE #xml_temp
SET NOCOUNT ON
   
CREATE TABLE #xml_temp
    (
      cwTSKRef NVARCHAR(20),
      xml_col XML,
      cwtskptyid INT
    )

INSERT  INTO #xml_temp
        (
          cwTSKRef,
          xml_col,
          cwtskptyid
        )
        SELECT  cwTSKRef,
                cwTSKDataBlock,
                cwtskptyid
        FROM    cwTSKRecords
                INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
        WHERE   cwTSKRef IN ( 'cwWILTPmntRecord', 'cwWILTClntDetail', 'cwWILTREGDetails','cwWILTDraftWill' )
                AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' )
       

               
               
SELECT  cwTSKPTYRecords.cwTSKPTYReference KBXRef,
        b.Title,
        b.Name,
        b.Address1,
        b.Address2,
        b.Address3,
        b.Address4,
        b.Address5,
        b.Address6,
        b.Postcode,
        b.Gender,
        b.Telephone,
        a.cwTSKDataBlock Email,
        c.Payment,
        cwTSKPTYRecords.CWSTDCreated InstrRec,
        a.cwtskptyid,
        d.cwTSKStatus,
        e.S1,
                e.S2,
                e.S3,
        e.children,
        e.guard,
        e.executor,
        e.funeral,
        e.Dist1,
        e.dist2,e.filter,
d.cwSTDLastUpdated
FROM    ( SELECT    cwTSKRef,
                    cwtskptyid,
                    cwTSKStatus,
                    cwTSKDataBlock
          FROM      cwTSKRecords
                    INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
          WHERE     cwTSKRef IN ( 'cwWILTREGDetails' )
                    AND cwTSKDataBlockRef = ( 'Interview' )
        ) a
        LEFT OUTER JOIN ( SELECT    cwtskptyid,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTitle" and @sc = "Person010000"]').value('.', 'varchar(max)') Title,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNName1" and @sc = "Person010000"]').value('.', 'varchar(max)') Name,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine1" and @sc = "Person010000"]').value('.', 'varchar(max)') Address1,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine2" and @sc = "Person010000"]').value('.', 'varchar(max)') Address2,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine3" and @sc = "Person010000"]').value('.', 'varchar(max)') Address3,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine4" and @sc = "Person010000"]').value('.', 'varchar(max)') Address4,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine5" and @sc = "Person010000"]').value('.', 'varchar(max)') Address5,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine6" and @sc = "Person010000"]').value('.', 'varchar(max)') Address6,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResPstCd" and @sc = "Person010000"]').value('.', 'varchar(max)') Postcode,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNGender1" and @sc = "Person010000"]').value('.', 'varchar(max)') Gender,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTelephone" and @sc = "Person010000"]').value('.', 'varchar(max)') Telephone
                          FROM      #xml_temp
                          WHERE     cwTSKRef = 'cwWILTClntDetail'
                        ) b ON a.cwtskptyid = b.cwtskptyid
        LEFT OUTER JOIN ( SELECT    cwTSKRef,
                                    cwTSKDataBlock Payment,
                                    cwtskptyid,
                                    cwTSKStatus
                          FROM      cwTSKRecords
                                    INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
                          WHERE     cwTSKRef = ( 'cwWILTPmntRecord' )
                                    AND cwTSKDataBlockRef = ( 'cwWILTPmntRecord' )
                        ) c ON a.cwtskptyid = c.cwtskptyid
        INNER JOIN cwTSKPTYRecords ON a.cwtskptyid = cwTSKPTYRecords.cwTSKPTYID
        INNER JOIN ( SELECT cwTSKRef,
                            cwtskptyid,
                            cwtskStatus,
cwSTDLastUpdated
                     FROM   cwTSKRecords
                     WHERE  cwTSKRef = ( 'cwWILTProgTsk' )
                            AND cwtskStatus <> 'H'
                   ) d ON a.cwtskptyid = d.cwtskptyid
                   LEFT OUTER JOIN ( SELECT    cwtskptyid,
                   xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "WILT0000"]').value('.', 'varchar(max)') S1,
                       xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "Person010000"]').value('.', 'varchar(max)') S2,  
                       xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCap0000"]').value('.', 'varchar(max)') S3,
                        xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCld0000"]').value('.', 'varchar(max)') Children,
                   xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwGU010000"]').value('.', 'varchar(max)') Guard,
             xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILExe0000"]').value('.', 'varchar(max)') Executor,
               xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwFU010000"]').value('.', 'varchar(max)') Funeral,
               xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD010000"]').value('.', 'varchar(max)') Dist1,
                xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD020000"]').value('.', 'varchar(max)') Dist2,
                xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwWILWNConf" and @sc = "WILT0000"]').value('.', 'varchar(max)') Filter
   

               
               
         
                          FROM      #xml_temp
                          WHERE     cwTSKRef = 'cwWILTDraftWill'
                        ) e ON a.cwtskptyid = e.cwtskptyid
WHERE   b.Name <> '' and not b.name like '%test%' and c.Payment is null

SET NOCOUNT OFF

DROP TABLE #xml_temp
Semi-colons were always allowed at the end of SQL statements, but previously not required.

As time passes, certain ;s are required.

So, add a semi-colon after every statement, that should fix it.
Cheers for the answer

I have started off as follows


IF OBJECT_ID('tempdb.dbo.#xml_temp') IS NOT NULL
    DROP TABLE #xml_temp;
SET NOCOUNT ON;
   
CREATE TABLE #xml_temp
    (
      cwTSKRef NVARCHAR(20),
      xml_col XML,
      cwtskptyid INT
    );

INSERT  INTO #xml_temp
        (
          cwTSKRef,
          xml_col,
          cwtskptyid
        );


        SELECT  cwTSKRef,
                cwTSKDataBlock,
                cwtskptyid
        FROM    cwTSKRecords
                INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
        WHERE   cwTSKRef IN ( 'cwWILTPmntRecord', 'cwWILTClntDetail', 'cwWILTREGDetails','cwWILTDraftWill' )
                AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' )

However it doesnt like the the semi colon after the Inset Into Statment, although it expects to see one. any ideas?
At the _end_ of the statement:


INSERT  INTO #xml_temp
        (
          cwTSKRef,
          xml_col,
          cwtskptyid
        )

        SELECT  cwTSKRef,
                cwTSKDataBlock,
                cwtskptyid
        FROM    cwTSKRecords
                INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
        WHERE   cwTSKRef IN ( 'cwWILTPmntRecord', 'cwWILTClntDetail', 'cwWILTREGDetails','cwWILTDraftWill' )
                AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' )
;
AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' ) ;  --<--
Thanks for your help

I have put the semi colns in the code as follows.

Still getting XML parsing: line 1, character 25, semicolon expected. any ideas?



IF OBJECT_ID('tempdb.dbo.#xml_temp') IS NOT NULL
    DROP TABLE #xml_temp;
SET NOCOUNT ON;
   
CREATE TABLE #xml_temp
    (
      cwTSKRef NVARCHAR(20),
      xml_col XML,
      cwtskptyid INT
    );

INSERT  INTO #xml_temp
        (
          cwTSKRef,
          xml_col,
          cwtskptyid
        )
       
        SELECT  cwTSKRef,
                cwTSKDataBlock,
                cwtskptyid
        FROM    cwTSKRecords
                INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
        WHERE   cwTSKRef IN ( 'cwWILTPmntRecord', 'cwWILTClntDetail', 'cwWILTREGDetails','cwWILTDraftWill' )
                AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' );    

               
               
SELECT  cwTSKPTYRecords.cwTSKPTYReference KBXRef,
        b.Title,
        b.Name,
        b.Address1,
        b.Address2,
        b.Address3,
        b.Address4,
        b.Address5,
        b.Address6,
        b.Postcode,
        b.Gender,
        b.Telephone,
        a.cwTSKDataBlock Email,
        c.Payment,
        cwTSKPTYRecords.CWSTDCreated InstrRec,
        a.cwtskptyid,
        d.cwTSKStatus,
        e.S1,
                e.S2,
                e.S3,
        e.children,
        e.guard,
        e.executor,
        e.funeral,
        e.Dist1,
        e.dist2,e.filter,
d.cwSTDLastUpdated
FROM    ( SELECT    cwTSKRef,
                    cwtskptyid,
                    cwTSKStatus,
                    cwTSKDataBlock
          FROM      cwTSKRecords
                    INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
          WHERE     cwTSKRef IN ( 'cwWILTREGDetails' )
                    AND cwTSKDataBlockRef = ( 'Interview' )
        ) a
        LEFT OUTER JOIN ( SELECT    cwtskptyid,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTitle" and @sc = "Person010000"]').value('.', 'varchar(max)') Title,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNName1" and @sc = "Person010000"]').value('.', 'varchar(max)') Name,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine1" and @sc = "Person010000"]').value('.', 'varchar(max)') Address1,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine2" and @sc = "Person010000"]').value('.', 'varchar(max)') Address2,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine3" and @sc = "Person010000"]').value('.', 'varchar(max)') Address3,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine4" and @sc = "Person010000"]').value('.', 'varchar(max)') Address4,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine5" and @sc = "Person010000"]').value('.', 'varchar(max)') Address5,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine6" and @sc = "Person010000"]').value('.', 'varchar(max)') Address6,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResPstCd" and @sc = "Person010000"]').value('.', 'varchar(max)') Postcode,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNGender1" and @sc = "Person010000"]').value('.', 'varchar(max)') Gender,
                                    xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTelephone" and @sc = "Person010000"]').value('.', 'varchar(max)') Telephone
                          FROM      #xml_temp
                          WHERE     cwTSKRef = 'cwWILTClntDetail'
                        ) b ON a.cwtskptyid = b.cwtskptyid
        LEFT OUTER JOIN ( SELECT    cwTSKRef,
                                    cwTSKDataBlock Payment,
                                    cwtskptyid,
                                    cwTSKStatus
                          FROM      cwTSKRecords
                                    INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
                          WHERE     cwTSKRef = ( 'cwWILTPmntRecord' )
                                    AND cwTSKDataBlockRef = ( 'cwWILTPmntRecord' )
                        ) c ON a.cwtskptyid = c.cwtskptyid
        INNER JOIN cwTSKPTYRecords ON a.cwtskptyid = cwTSKPTYRecords.cwTSKPTYID
        INNER JOIN ( SELECT cwTSKRef,
                            cwtskptyid,
                            cwtskStatus,
cwSTDLastUpdated
                     FROM   cwTSKRecords
                     WHERE  cwTSKRef = ( 'cwWILTProgTsk' )
                            AND cwtskStatus <> 'H'
                   ) d ON a.cwtskptyid = d.cwtskptyid
                   LEFT OUTER JOIN ( SELECT    cwtskptyid,
                   xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "WILT0000"]').value('.', 'varchar(max)') S1,
                       xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "Person010000"]').value('.', 'varchar(max)') S2,  
                       xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCap0000"]').value('.', 'varchar(max)') S3,
                        xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCld0000"]').value('.', 'varchar(max)') Children,
                   xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwGU010000"]').value('.', 'varchar(max)') Guard,
             xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILExe0000"]').value('.', 'varchar(max)') Executor,
               xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwFU010000"]').value('.', 'varchar(max)') Funeral,
               xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD010000"]').value('.', 'varchar(max)') Dist1,
                xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD020000"]').value('.', 'varchar(max)') Dist2,
                xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwWILWNConf" and @sc = "WILT0000"]').value('.', 'varchar(max)') Filter
             
               
         
                          FROM      #xml_temp
                          WHERE     cwTSKRef = 'cwWILTDraftWill'
                        ) e ON a.cwtskptyid = e.cwtskptyid
WHERE   b.Name <> '' and not b.name like '%test%' and c.Payment is null;

SET NOCOUNT OFF;

DROP TABLE #xml_temp;
Did you look at my answer? That error tells me that it is a problem with the data and not your actual query. What character is at char 25 in your xml file?