Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parsing Problem semi colon expected

Posted on 2010-09-08
10
Medium Priority
?
4,113 Views
Last Modified: 2012-05-10
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?

0
Comment
Question by:halifaxman
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 33626405
post all of your code.
0
 
LVL 11

Accepted Solution

by:
dodge20 earned 2000 total points
ID: 33626515
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
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33626526
I think SQL Server 2008 now allows Temp tables (# table), you need to use temp table variable .
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:halifaxman
ID: 33626854
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33628378
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
 

Author Comment

by:halifaxman
ID: 33635183
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33640761
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33640780
AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' ) ;  --<--
0
 

Author Comment

by:halifaxman
ID: 33644622
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
 
LVL 11

Expert Comment

by:dodge20
ID: 33645366
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question