float and money Data conversion between ado com and ado dotnet not the same with sqlserve

I am running the same query through the traditional ado com interface and
through dotnet ado. The query is a FOR XML EXPLICIT and in ado com I am
using the streaming capability to retrieve the xml and in dotnet the
ExecuteXmlreader command.
The result for ado com is :
<results><motor.cycle id="177496" ver="1" sum.insured="100000"
model="25089700" cover.type.id="0" make="546" capacity="1000" .....
The result for ado .net and in sql analyzer is :
<motor.cycle id="177496" ver="1" sum.insured="100000.0000" model="25089700"
cover.type.id="0" make="546" capacity="1.000000000000000e+003".....
It is clear that the money fields(sum..insured) and float fields(capacity)
gets converted(implicitly by SQL?) in different ways.
Does anyone know why it is not the same and how to get it the same?
This is most likely an artefact of the XML serialization of the typed values
being done differently by the two providers (for some providers and FOR XML
queries, we are sending binary data over that then gets converted to strings
in the providers).
The ADO.Net serialization is consistent with the server-side serialization:
select cast(1000 as float) as x for XML raw, type
select Cast((select cast(1000 as float) as x for XML raw, type) as
nvarchar(max))
Note that management studio also uses ADO.Net.
If this is problematic, please let me know. Note that I am not sure however,
that we can change it now...
Best regards
Michael
"Eben" <eben@.fspsolutions.com> wrote in message
news:eGKV5vF5FHA.1956@.TK2MSFTNGP09.phx.gbl...
>I am running the same query through the traditional ado com interface and
>through dotnet ado. The query is a FOR XML EXPLICIT and in ado com I am
>using the streaming capability to retrieve the xml and in dotnet the
>ExecuteXmlreader command.
> The result for ado com is :
> <results><motor.cycle id="177496" ver="1" sum.insured="100000"
> model="25089700" cover.type.id="0" make="546" capacity="1000" .....
> The result for ado .net and in sql analyzer is :
> <motor.cycle id="177496" ver="1" sum.insured="100000.0000"
> model="25089700" cover.type.id="0" make="546"
> capacity="1.000000000000000e+003".....
> It is clear that the money fields(sum..insured) and float fields(capacity)
> gets converted(implicitly by SQL?) in different ways.
>
> Does anyone know why it is not the same and how to get it the same?
>

float and money Data conversion between ado com and ado dotnet not the same with sql

I am running the same query through the traditional ado com interface and
through dotnet ado. The query is a FOR XML EXPLICIT and in ado com I am
using the streaming capability to retrieve the xml and in dotnet the
ExecuteXmlreader command.
The result for ado com is :
<results><motor.cycle id="177496" ver="1" sum.insured="100000"
model="25089700" cover.type.id="0" make="546" capacity="1000" .....
The result for ado .net and in sql analyzer is :
<motor.cycle id="177496" ver="1" sum.insured="100000.0000" model="25089700"
cover.type.id="0" make="546" capacity="1.000000000000000e+003".....
It is clear that the money fields(sum..insured) and float fields(capacity)
gets converted(implicitly by SQL?) in different ways.
Does anyone know why it is not the same and how to get it the same?This is most likely an artefact of the XML serialization of the typed values
being done differently by the two providers (for some providers and FOR XML
queries, we are sending binary data over that then gets converted to strings
in the providers).
The ADO.Net serialization is consistent with the server-side serialization:
select cast(1000 as float) as x for XML raw, type
select Cast((select cast(1000 as float) as x for XML raw, type) as
nvarchar(max))
Note that management studio also uses ADO.Net.
If this is problematic, please let me know. Note that I am not sure however,
that we can change it now...
Best regards
Michael
"Eben" <eben@.fspsolutions.com> wrote in message
news:eGKV5vF5FHA.1956@.TK2MSFTNGP09.phx.gbl...
>I am running the same query through the traditional ado com interface and
>through dotnet ado. The query is a FOR XML EXPLICIT and in ado com I am
>using the streaming capability to retrieve the xml and in dotnet the
>ExecuteXmlreader command.
> The result for ado com is :
> <results><motor.cycle id="177496" ver="1" sum.insured="100000"
> model="25089700" cover.type.id="0" make="546" capacity="1000" .....
> The result for ado .net and in sql analyzer is :
> <motor.cycle id="177496" ver="1" sum.insured="100000.0000"
> model="25089700" cover.type.id="0" make="546"
> capacity="1.000000000000000e+003".....
> It is clear that the money fields(sum..insured) and float fields(capacity)
> gets converted(implicitly by SQL?) in different ways.
>
> Does anyone know why it is not the same and how to get it the same?
>

Flatten out an XML Document (Currently using OPENXML)

I am attempting to take a column with an XML datatype and make it available for reporting with as little code as possible. Specifically, we are storing credit report info in a column that has an XML datatype. We are using the OPENXML command to navigate the XML structure:

DECLARE @.idoc int
DECLARE @.doc xml
SET @.doc = 'GET THE XML COLUMN HERE
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/XML_INTERFACE/CREDITREPORT/OBJECTS/DBCOMMCREDITSCORE',2)
WITH (
Rating varchar(10) 'DBRATING',
OverFlow xml '@.mp:xmltext')
EXEC sp_xml_removedocument @.idoc

The result will be a column that shows the Rating field and an Overflow column that shows the XML string.

I can take the above code and put it in a stored procedure to return the values in a flat format. My questions:

Does anyone know how I can use a view to display the information? (Create View cannot have the above statements. Is there a way to create a tabular representation off of the SP result set, etc.)
Does anyone have a better way of taking an XML datatype and making this available for report writers?

Dan O

1. The XQuery "nodes()" function will work for this. It is similar to OPENXML, but it is just used from within a normal SELECT statement, so you could create views on top of it.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

2. nodes() seems like the way to go, but you could also try pre-shredding the xml into a more convenient relational structure if your XML has a lot of nesting that would require many joins to reassemble.

flat file to xml

Hey, all. Is it possible to read a flat file in and convert it to xml in SSIS? Xml is not listed as one of the destination types... OR, is there some easy way to take a fixed width flat file and convert it to xml?

Thanks for any insight!

Jim Work

SSIS does not include an XML destinaiton component. The explanation that I've heard is that this is because XML is too rich and complex a format to easily map to. At least in the first release.

You should be able to do this using a Script Component as a destination, if you have a known XML format to which you should be writing.

|||"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim
|||

It's my pleasure to help. It's always great to see how people are using SSIS, and always better to learn from others' pain than from my own.

Take a look at this page: http://msdn2.microsoft.com/en-us/sql/aa336314.aspx

There are sample component source code projects and tutorials/documentation on creating custom SSIS components available here for download.

|||

Jim Work wrote:

"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim

I've run into this a few times recently myself, so I created an example and posted it to my blog. Hope it helps.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.