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?

Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
post all of your code.
0
dodge20Commented:
Do you have an ampersand(&) in the data? I am assuming you are loading an xml file into the temp table.

This article explains how to encode an ampersand.
http://beyondrelational.com/blogs/community/archive/2010/05/24/sql-server-xml-un-making-a-list-or-shredding-of-evidence.aspx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohit VijayCommented:
I think SQL Server 2008 now allows Temp tables (# table), you need to use temp table variable .
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WilsonBI DeveloperAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
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.
0
Mark WilsonBI DeveloperAuthor Commented:
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?
0
Scott PletcherSenior DBACommented:
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' )
;
0
Scott PletcherSenior DBACommented:
AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' ) ;  --<--
0
Mark WilsonBI DeveloperAuthor Commented:
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;
0
dodge20Commented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.