Solved

Parsing Problem semi colon expected

Posted on 2010-09-08
10
3,231 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
Comment Utility
post all of your code.
0
 
LVL 11

Accepted Solution

by:
dodge20 earned 500 total points
Comment Utility
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
Comment Utility
I think SQL Server 2008 now allows Temp tables (# table), you need to use temp table variable .
0
 

Author Comment

by:halifaxman
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:halifaxman
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' ) ;  --<--
0
 

Author Comment

by:halifaxman
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now