Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parsing Problem semi colon expected

Posted on 2010-09-08
10
Medium Priority
?
3,928 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

664 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