February 3, 2015 at 3:17 am
Hi,
I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name "Length" set up as Decimal(18,2) and it stops on the first row with an error, something like "Cannot convert to decimal". The values are all integers, such as "9", etc. but I presumed SQL would convert to "9.00" for example. It has worked for another field name "Weight", where values are stored in the XML file such as "0.28", etc. Does it reject it because it's an integer and needs to be to two decimal format in the XML?
Thanks
February 3, 2015 at 4:20 am
Can you again check the xml whether it has string for any records
February 3, 2015 at 7:00 am
Hi, I'd already checked the whole file and all are entered as integers. It stops inserting at the first record, at the "Length" element, which is "9".
February 3, 2015 at 7:46 am
Check the XML for hidden characters as this would stop the implicit decimal conversion
Far away is close at hand in the images of elsewhere.
Anon.
February 3, 2015 at 8:39 am
OK but like I said it stops inserting before the first record and I presume it's to do with the characters in the field of the first row. I know the character is "9" that's it.
February 3, 2015 at 10:14 am
ma701ss (2/3/2015)
OK but like I said it stops inserting before the first record and I presume it's to do with the characters in the field of the first row. I know the character is "9" that's it.
This works:
select convert(decimal(18,2),'9')
When you are debugging, you really need to also test your assumptions, and one of your assumptions is the character string you are converting is "9".
You could try inserting to a different destination that has a varchar instead of a decimal(18,2), and then seeing what you actually inserted.
Obviously I could be completely mistaken :hehe: :hehe: :hehe:
February 3, 2015 at 10:18 am
OK but like I said it stops inserting before the first record and I presume it's to do with the characters in the field of the first row.
But we cannot see the file or the TSQL or the table DDL
I know the character is "9" that's it.
Have you checked the file with a hex viewer?
Far away is close at hand in the images of elsewhere.
Anon.
February 3, 2015 at 10:27 am
convert the value to varchar instead and a temp table , and do a CASE WHEN ISNumeric(ConvertedValue) = 0 THEN 'whoops' ELSE' good conversion' END.
as everyone else has already identified, a value exists in your data that cannot be converted. you might be fixated on '9', and it's another value, or it might be the next value in the xml;
an empty string for example can implicitly convert to integer but not decimal, i believe.
without the file itself for us to look at, we can offer advice only.
if you showed us your exact query you are using, we could help bullet proof that.
Lowell
February 3, 2015 at 3:12 pm
I've just imported everything in my XML file as varchar and then managed to convert some of the fields to Decimal(18,2) in SQL Server Management Studio however some apparently numeric fields with e.g. single digits or up to two decimal places, will only convert to float. Why would for example "9" or "9.00" not convert to decimal but will convert to float?
February 4, 2015 at 1:50 am
ma701ss (2/3/2015)
Hi,I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name "Length" set up as Decimal(18,2) and it stops on the first row with an error, something like "Cannot convert to decimal". The values are all integers, such as "9", etc. but I presumed SQL would convert to "9.00" for example. It has worked for another field name "Weight", where values are stored in the XML file such as "0.28", etc. Does it reject it because it's an integer and needs to be to two decimal format in the XML?
Thanks
Quick question, can you post a sample XML and the DDL for the destination table? Without this, any response to you post are pure guesswork and the answer would therefore be 42.
😎
February 4, 2015 at 7:02 am
ma701ss (2/3/2015)
I've just imported everything in my XML file as varchar and then managed to convert some of the fields to Decimal(18,2) in SQL Server Management Studio however some apparently numeric fields with e.g. single digits or up to two decimal places, will only convert to float. Why would for example "9" or "9.00" not convert to decimal but will convert to float?
I agree with everyone else, we need to see more. Treat it like a tech issue that you're taking to microsoft, provide an exact scenario that can reproduce the failure without displaying company confidential information. Like for instance:
"I am having trouble converting valid numeric data to decimal(18,2), the first line below works but the second line seems to fail for me on sql server version 2012, and I therefore believe that there is a bug in your product! Can you provide a patch, thanks!"
-- THIS WORKS
select convert(decimal(18,2),N'9.00')
-- THIS DOES NOT
select convert(decimal(18,2),N'? ?? ??? ???')
February 4, 2015 at 2:53 pm
Below is the XML. PRICE and RRP convert fine to Decimal(18,2). However LENGTH, DIAMETER, and WIDTH will not but they will convert to float in SQL Server.
<?xml version="1.0"?>
<STOREITEMS>
<CREATED>
<CATEGORY id="127" name="Category 1">
<PRODUCT ITEM="4023">
<NAME>Patriot</NAME>
<MODEL>7505</MODEL>
<PRICE>10.46</PRICE>
<RRP>20.00</RRP>
<THUMB>07505ds.jpg</THUMB>
<IMAGE>07505.jpg</IMAGE>
<MULTI/>
<MULTI1/>
<MULTI2/>
<MULTI3/>
<BIGMULTI1/>
<BIGMULTI2/>
<BIGMULTI3/>
<DESCRIPTION>Description.</DESCRIPTION>
<XIMAGE>07505x.jpg</XIMAGE>
<XIMAGE2>07505x1.jpg</XIMAGE2>
<XIMAGE3>07505x2.jpg</XIMAGE3>
<XIMAGE4/>
<XIMAGE5/>
<LENGTH>9</LENGTH>
<DIAMETER>1.75</DIAMETER>
<ORINGCIRC/>
<ORINGDIAM/>
<WIDTH>5</WIDTH>
<CONTROLLER>Built In</CONTROLLER>
<WHATISIT/>
<FOR/>
<MOTION/>
<FEATURES>Multi speed</FEATURES>
<MISC/>
<STYLE/>
<POWER>2 x AA (Included)</POWER>
<SIZE>7</SIZE>
<ATTRIBUTES NAME="Batteries" ATTRIBUTEID="9">
<ATTRIBUTEVALUES VALUE="190" TITLE="None" PRICEADJUST="+0.00"/>
<ATTRIBUTEVALUES VALUE="182" TITLE="2 x AA" PRICEADJUST="+0.30"/>
</ATTRIBUTES>
</PRODUCT>
</CATEGORY>
</CREATED>
</STOREITEMS>
February 4, 2015 at 3:25 pm
This should get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<?xml version="1.0"?>
<STOREITEMS>
<CREATED>
<CATEGORY id="127" name="Category 1">
<PRODUCT ITEM="4023">
<NAME>Patriot</NAME>
<MODEL>7505</MODEL>
<PRICE>10.46</PRICE>
<RRP>20.00</RRP>
<THUMB>07505ds.jpg</THUMB>
<IMAGE>07505.jpg</IMAGE>
<MULTI/>
<MULTI1/>
<MULTI2/>
<MULTI3/>
<BIGMULTI1/>
<BIGMULTI2/>
<BIGMULTI3/>
<DESCRIPTION>Description.</DESCRIPTION>
<XIMAGE>07505x.jpg</XIMAGE>
<XIMAGE2>07505x1.jpg</XIMAGE2>
<XIMAGE3>07505x2.jpg</XIMAGE3>
<XIMAGE4/>
<XIMAGE5/>
<LENGTH>9</LENGTH>
<DIAMETER>1.75</DIAMETER>
<ORINGCIRC/>
<ORINGDIAM/>
<WIDTH>5</WIDTH>
<CONTROLLER>Built In</CONTROLLER>
<WHATISIT/>
<FOR/>
<MOTION/>
<FEATURES>Multi speed</FEATURES>
<MISC/>
<STYLE/>
<POWER>2 x AA (Included)</POWER>
<SIZE>7</SIZE>
<ATTRIBUTES NAME="Batteries" ATTRIBUTEID="9">
<ATTRIBUTEVALUES VALUE="190" TITLE="None" PRICEADJUST="+0.00"/>
<ATTRIBUTEVALUES VALUE="182" TITLE="2 x AA" PRICEADJUST="+0.30"/>
</ATTRIBUTES>
</PRODUCT>
</CATEGORY>
</CREATED>
</STOREITEMS>';
SELECT
CATEGORY.DATA.value('@id','INT') AS CATEGORY_id
,PRODUCT.DATA.value('WIDTH[1]','DECIMAL(18,2)') AS PRODUCT_WIDTH
FROM @TXML.nodes('STOREITEMS/CREATED/CATEGORY') AS CATEGORY(DATA)
OUTER APPLY CATEGORY.DATA.nodes('PRODUCT') AS PRODUCT(DATA)
;
Results
CATEGORY_id PRODUCT_WIDTH
----------- ---------------
127 5.00
February 4, 2015 at 3:35 pm
I don't understand that. Why do you think it worked for you? If you don't mind, what happens if you import the XML with every field as varchar(50) and then try to convert in SSMS the WIDTH field to Decimal(18,2)? My data is imported now and I've changed the data types to float where decimal wouldn't work but I'm just curious why it accepts float but not decimal. They're basically the same?
February 4, 2015 at 10:29 pm
ma701ss (2/4/2015)
I don't understand that. Why do you think it worked for you? If you don't mind, what happens if you import the XML with every field as varchar(50) and then try to convert in SSMS the WIDTH field to Decimal(18,2)? My data is imported now and I've changed the data types to float where decimal wouldn't work but I'm just curious why it accepts float but not decimal. They're basically the same?
First guess would be the file itself or even collation settings although the latter is a very remote possibility. The sample you posted has been "sanitized" in a sense as no unwanted/hidden characters get passed through the html of the forum system.
😎
Try run the code below on your system, copying and pasting your actual data into the variable.
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<?xml version="1.0"?>
<STOREITEMS>
<CREATED>
<CATEGORY id="127" name="Category 1">
<PRODUCT ITEM="4023">
<NAME>Patriot</NAME>
<MODEL>7505</MODEL>
<PRICE>10.46</PRICE>
<RRP>20.00</RRP>
<THUMB>07505ds.jpg</THUMB>
<IMAGE>07505.jpg</IMAGE>
<MULTI/>
<MULTI1/>
<MULTI2/>
<MULTI3/>
<BIGMULTI1/>
<BIGMULTI2/>
<BIGMULTI3/>
<DESCRIPTION>Description.</DESCRIPTION>
<XIMAGE>07505x.jpg</XIMAGE>
<XIMAGE2>07505x1.jpg</XIMAGE2>
<XIMAGE3>07505x2.jpg</XIMAGE3>
<XIMAGE4/>
<XIMAGE5/>
<LENGTH>9</LENGTH>
<DIAMETER>1.75</DIAMETER>
<ORINGCIRC/>
<ORINGDIAM/>
<WIDTH>5</WIDTH>
<CONTROLLER>Built In</CONTROLLER>
<WHATISIT/>
<FOR/>
<MOTION/>
<FEATURES>Multi speed</FEATURES>
<MISC/>
<STYLE/>
<POWER>2 x AA (Included)</POWER>
<SIZE>7</SIZE>
<ATTRIBUTES NAME="Batteries" ATTRIBUTEID="9">
<ATTRIBUTEVALUES VALUE="190" TITLE="None" PRICEADJUST="+0.00"/>
<ATTRIBUTEVALUES VALUE="182" TITLE="2 x AA" PRICEADJUST="+0.30"/>
</ATTRIBUTES>
</PRODUCT>
</CATEGORY>
</CREATED>
</STOREITEMS>';
;WITH BASE_DATA AS
(
SELECT
CATEGORY.DATA.value('@id' ,'INT') AS CATEGORY_id
,PRODUCT.DATA.value('NAME[1]' ,'varchar(60)') AS PRODUCT_NAME
,PRODUCT.DATA.value('MODEL[1]' ,'varchar(60)') AS PRODUCT_MODEL
,PRODUCT.DATA.value('PRICE[1]' ,'varchar(60)') AS PRODUCT_PRICE
,PRODUCT.DATA.value('RRP[1]' ,'varchar(60)') AS PRODUCT_RRP
,PRODUCT.DATA.value('THUMB[1]' ,'varchar(60)') AS PRODUCT_THUMB
,PRODUCT.DATA.value('IMAGE[1]' ,'varchar(60)') AS PRODUCT_IMAGE
,PRODUCT.DATA.value('LENGTH[1]' ,'varchar(60)') AS PRODUCT_LENGTH
,PRODUCT.DATA.value('DIAMETER[1]','varchar(60)') AS PRODUCT_DIAMETER
,PRODUCT.DATA.value('WIDTH[1]' ,'varchar(60)') AS PRODUCT_WIDTH
FROM @TXML.nodes('STOREITEMS/CREATED/CATEGORY') AS CATEGORY(DATA)
OUTER APPLY CATEGORY.DATA.nodes('PRODUCT') AS PRODUCT(DATA)
)
SELECT
BD.CATEGORY_id
,BD.PRODUCT_NAME
,BD.PRODUCT_MODEL
,CONVERT(DECIMAL(18,2),BD.PRODUCT_PRICE,1)AS PRODUCT_PRICE
,CONVERT(DECIMAL(18,2),BD.PRODUCT_RRP,1) AS PRODUCT_RRP
,BD.PRODUCT_THUMB
,BD.PRODUCT_IMAGE
,CONVERT(DECIMAL(18,2),BD.PRODUCT_LENGTH,1)AS PRODUCT_LENGTH
,CONVERT(DECIMAL(18,2),BD.PRODUCT_DIAMETER,1)AS PRODUCT_DIAMETER
,CONVERT(DECIMAL(18,2),BD.PRODUCT_WIDTH,1) AS PRODUCT_WIDTH
FROM BASE_DATA BD
;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply