replace /case when for XML - 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)

replace /case when for XML

Author

Topic

hoggy
Starting Member

11 Posts

Posted - 2012-06-13 : 05:48:24

Hi I have some XML data being sent from a web form. The XML nodes have datatypes. My problem is: if the user does not fill in a value for a numeric field a blank string is fed to the XML which then crashes due to incorrect datatype (varchar instead of numeric). I want to do something similar to case when to replace "" with "0".

Here is my XML CTE with problem:
declare @XMLSales XML

set @XMLSales =N'<root>

<row ID=''123'' Sales='''' />
<row ID=''456'' Sales=''0'' />
</root>'

;WITH CTE AS (
SELECT
x.h.value('@ID', 'VARCHAR(3)') AS ID,
x.h.value('@Sales', 'decimal(18,0)') AS Sales
FROM @XMLSales.nodes('/root/row') AS x(h)
)

Select * from CTE

hoggy
Starting Member

11 Posts

Posted - 2012-06-13 : 05:58:52

decided to do fix in web app

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:44:35

In sql bring it as string, do a blank check and convert it to '0' if blank and then do convertion. then it will not throw incorrect data type error


;WITH CTE AS ( 
SELECT 
x.h.value('@ID', 'VARCHAR(3)') AS ID, 
x.h.value('@Sales', 'varchar(18)') AS Sales 
FROM @XMLSales.nodes('/root/row') AS x(h) 
)

SELECT ID,CASE WHEN Sales > '' THEN Sales ELSE 0 END FROM CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-13 : 12:50:20

Why not use FLOAT? FLOAT always handles integer part correctly (it not more than 15 characters anyway).

DECLARE @XMLSales XML =	'
				<root>
					<row ID="123" Sales="" />
					<row ID="456" Sales="0" />
				</root>
			' 

SELECT	x.h.value('@ID', 'VARCHAR(3)') AS ID, 
	x.h.value('@Sales', 'FLOAT') AS Sales 
FROM	@XMLSales.nodes('/root/row') AS x(h)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources