Implicit COnversion Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Implicit COnversion Error

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-09-19 : 07:44:56

I have a lot of xml reports to import to different tables. I am trying to open each report and find out which type it is, and save the type into a variable

DECLARE @ReportType VARCHAR(10)

SELECT @ReportType = CASE
WHEN t.u.value('(InputReport/Header/@reportType)[1]','varchar(20)') = 'REFT2013' THEN 'INPUT'
WHEN t.u.value('(ARUDD/Header/@reportType)[1]','varchar(20)') = 'REFT1019' THEN 'ARUDD'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7001' THEN 'ADDACS'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7003' THEN 'AUDACC'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7007' THEN 'AWACS'
ELSE d.Xmlreport
END
FROM
(SELECT CAST(CAST([XMLText] AS NTEXT) AS XML) AS Xmlreport
FROM MYTABLE WHERE UserNumber <> '' ) d
CROSS APPLY Xmlreport.nodes('/BACSDocument/Data')t(u)

I keep getting an error
Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 08:35:34

[code]DECLARE @ReportType VARCHAR(10)

SELECT @ReportType = CASE
WHEN t.u.value('(InputReport/Header/@reportType)[1]','varchar(20)') = 'REFT2013' THEN 'INPUT'
WHEN t.u.value('(ARUDD/Header/@reportType)[1]','varchar(20)') = 'REFT1019' THEN 'ARUDD'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7001' THEN 'ADDACS'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7003' THEN 'AUDACC'
WHEN t.u.value('(MessagingAdvices/MessagingHeader/@report-type)[1]','varchar(20)') = '7007' THEN 'AWACS'
ELSE CAST(d.Xmlreport AS VARCHAR(MAX))
END
FROM
(SELECT CAST(CAST([XMLText] AS NTEXT) AS XML) AS Xmlreport
FROM MYTABLE WHERE UserNumber <> '' ) d
CROSS APPLY Xmlreport.nodes('/BACSDocument/Data')t(u)

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources