September 21, 2010 at 4:06 am
I am having a problem with the query below, this worked fine until the database it was querying was upgraded to SQL 2008
I now get the following error
Msg 9411, Level 16, State 1, Line 13
XML parsing: line 1, character 25, semicolon expected
Can anybody help with this?
Thanks
1IF OBJECT_ID('tempdb.dbo.#xml_temp') IS NOT NULL
2 DROP TABLE #xml_temp
3SET NOCOUNT ON
4
5CREATE TABLE #xml_temp
6 (
7 cwTSKRef NVARCHAR(20),
8 xml_col XML,
9 cwtskptyid INT
10 )
11
12
13INSERT into #xml_temp
14 (
15 cwTSKRef,
16 xml_col,
17 cwtskptyid
18 )
19 SELECT cwTSKRef,
20 cwTSKDataBlock,
21 cwtskptyid
22 FROM cwTSKRecords
23 INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
24 WHERE cwTSKRef IN ( 'cwWILTPmntRecord', 'cwWILTClntDetail',
25 'cwWILTREGDetails', 'cwWILTDraftWill' )
26 AND cwTSKDataBlockRef IN ( 'Interview', 'cwWILTPmntRecord' )
27
28SELECT cwTSKPTYRecords.cwTSKPTYReference KBXRef,
29 b.Title,
30 b.Name,
31 b.Address1,
32 b.Address2,
33 b.Address3,
34 b.Address4,
35 b.Address5,
36 b.Address6,
37 b.Postcode,
38 b.Gender,
39 b.Telephone,
40 a.cwTSKDataBlock Email,
41 c.Payment,
42 cwTSKPTYRecords.CWSTDCreated InstrRec,
43 a.cwtskptyid,
44 d.cwTSKStatus,
45 e.S1,
46 e.S2,
47 e.S3,
48 e.children,
49 e.guard,
50 e.executor,
51 e.funeral,
52 e.Dist1,
53 e.dist2,
54 e.filter,
55 d.cwSTDLastUpdated
56FROM ( SELECT cwTSKRef,
57 cwtskptyid,
58 cwTSKStatus,
59 cwTSKDataBlock
60 FROM cwTSKRecords
61 INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
62 WHERE cwTSKRef IN ( 'cwWILTREGDetails' )
63 AND cwTSKDataBlockRef = ( 'Interview' )
64 ) a
65 LEFT OUTER JOIN ( SELECT cwtskptyid,
66 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTitle" and @sc = "Person010000"]').value('.', 'varchar(max)') Title,
67 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNName1" and @sc = "Person010000"]').value('.', 'varchar(max)') Name,
68 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine1" and @sc = "Person010000"]').value('.', 'varchar(max)') Address1,
69 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine2" and @sc = "Person010000"]').value('.', 'varchar(max)') Address2,
70 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine3" and @sc = "Person010000"]').value('.', 'varchar(max)') Address3,
71 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine4" and @sc = "Person010000"]').value('.', 'varchar(max)') Address4,
72 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine5" and @sc = "Person010000"]').value('.', 'varchar(max)') Address5,
73 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResLine6" and @sc = "Person010000"]').value('.', 'varchar(max)') Address6,
74 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNAddResPstCd" and @sc = "Person010000"]').value('.', 'varchar(max)') Postcode,
75 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNGender1" and @sc = "Person010000"]').value('.', 'varchar(max)') Gender,
76 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwPSNTelephone" and @sc = "Person010000"]').value('.', 'varchar(max)') Telephone
77 FROM #xml_temp
78 WHERE cwTSKRef = 'cwWILTClntDetail'
79 ) b ON a.cwtskptyid = b.cwtskptyid
80 LEFT OUTER JOIN ( SELECT cwTSKRef,
81 cwTSKDataBlock Payment,
82 cwtskptyid,
83 cwTSKStatus
84 FROM cwTSKRecords
85 INNER JOIN cwTSKDataBlocks ON cwTSKRecords.ID = cwTSKDataBlocks.cwTSKRECID
86 WHERE cwTSKRef = ( 'cwWILTPmntRecord' )
87 AND cwTSKDataBlockRef = ( 'cwWILTPmntRecord' )
88 ) c ON a.cwtskptyid = c.cwtskptyid
89 INNER JOIN cwTSKPTYRecords ON a.cwtskptyid = cwTSKPTYRecords.cwTSKPTYID
90 INNER JOIN ( SELECT cwTSKRef,
91 cwtskptyid,
92 cwtskStatus,
93 cwSTDLastUpdated
94 FROM cwTSKRecords
95 WHERE cwTSKRef = ( 'cwWILTProgTsk' )
96 AND cwtskStatus <> 'H'
97 ) d ON a.cwtskptyid = d.cwtskptyid
98 LEFT OUTER JOIN ( SELECT cwtskptyid,
99 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "WILT0000"]').value('.', 'varchar(max)') S1,
100 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "Person010000"]').value('.', 'varchar(max)') S2,
101 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCap0000"]').value('.', 'varchar(max)') S3,
102 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILCld0000"]').value('.', 'varchar(max)') Children,
103 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwGU010000"]').value('.', 'varchar(max)') Guard,
104 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILExe0000"]').value('.', 'varchar(max)') Executor,
105 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwFU010000"]').value('.', 'varchar(max)') Funeral,
106 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD010000"]').value('.', 'varchar(max)') Dist1,
107 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwOSQuestionsGRP" and @sc = "cwWILD020000"]').value('.', 'varchar(max)') Dist2,
108 xml_col.query('//Ref[@ID = "tmp" and @sf="tmp.cwWILWNConf" and @sc = "WILT0000"]').value('.', 'varchar(max)') Filter
109 FROM #xml_temp
110 WHERE cwTSKRef = 'cwWILTDraftWill'
111 ) e ON a.cwtskptyid = e.cwtskptyid
112WHERE b.Name <> ''
113 AND NOT b.name LIKE '%test%'
114 AND c.Payment IS NULL
115
116SET NOCOUNT OFF
117
118DROP TABLE #xml_temp
September 21, 2010 at 4:44 am
You'll need to give more information than that for people to be able to properly help. Have a look here for more information:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
As a stab in the dark, I doubt it has anything to do with moving to SQL 2008 - that error usually occurs when your XML data is invalid - e.g. due to reserved characters like ampersands.
September 21, 2010 at 7:11 am
I've got to agree with Howard on this one. The code parses in SSMS without issue which means it is probably an error in the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply