Implicit Text to Binary Conversion

/***
Requirement: Insert ascii text into varbinary field allowing SQL to convert
the data implicitly
Problem: The data is encoded as Unicode and is truncated or simply blanked
out after the first 10 characters
The following is valid and works; however, it doesn't solve my problem.
As a result, I've tried several approaches, including creating a cursor and
calling Cast() to convert the data to binary. This appears to work, but
again, stops after the first 10 characters
**/
DECLARE @.docHandle int
DECLARE @.xmlDoc varchar(5000)
SET @.xmlDoc ='
<?xml version="1.0"?>
<f2dbarchive>
<archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
<archive_test id="1530" />
<!-- tables used in this archive -->
<tables>
<Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
lngKeyValue="2096" lngRecordCount="4" />
</tables>
<!-- data from the tables -->
<data>
<tblFinalData>
<Row lngDataID="5336" lngInstanceID="2096" binData="10946534210000000000276
Final
35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
0.085 DUST SPOTTER 3 LOF 85.00
NY3849.475909519025.7722760243122.0620566407829311 98.67 32086.86
160 85 31198.67 32086.86
140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
123.21566117 -0.30555699 0.0010055 0.00555699
162.7038151636657.46 2017 Y Y34642.169 33069.594
172.887 MND 5.5B_NEW
147.95450669 -0.39920593 0.02882665 0.34920593 167.814226706570
2019 N N31198.912 32050.403 140.93 GP
8 OBS PT 0 0
-0.000382 0.01695564 0 0 1658 Y
Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
203.64517087 -1.08627593 0.00482913 0.03627593
163.7961106000857.63 2015 N N31892.772 32960.261
153.253 OBS PT 3B SOUTH 0
0 0.00531249 0.11706955 0 0
"
intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0" />
<Row lngDataID="5337" lngInstanceID="2096" binData="10946539640000000000277
Final
34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
0 1ST LIGHT SPOTTER 3
NY3717.384364552516.449301983071517.08833337179311 98.67
32086.86 160 85 31198.67
32086.86 140.48 85 0 0 1656
Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH
PED 128.4489945 34.57777634 0.0010055
0.00555699 829.755965865400 2017 Y Y34642.169
33069.594 172.887 MND 5.5B_NEW
158.30450669 44.65079407 0.02882665 0.34920593
856.898004060910 2019 N N31198.912 32050.403
140.93 GP 8 OBS PT 0
0 -0.000382 0.01695564 0 0 1658
N N35282.726 33023.024 175.67 TOWER @. 6B
SOUTH PED 0 0
0.00482913 0.03627593 0 0 2015 Y
Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
100.09468751 12.76626379 0.00531249 0.11706955
845.995612034910
" intPackageCount="" booGood="1" txtShotID=""
txtStatus="Field" qvTime="0" />
</tblFinalData>
</data>
</f2dbarchive>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
DECLARE @.tableName varchar(80)
DECLARE @.instID int
SET @.instID=2096
SET @.tableName='tblFinalData'
IF(SELECT lngRecordCount FROM OPENXML(@.docHandle,
'/f2dbarchive/tables/Row', 1)
WITH (txtTablename varchar(80), lngRecordCount int)
WHERE txtTablename=@.tableName) > 0
BEGIN
print 'at least 1 row in '+@.tableName
INSERT INTO tblFinalData
(lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
qvTime)
SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
booGood, txtShotID, txtStatus, qvTime
FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
WITH (
lngInstanceID int,
binData varbinary(2000),
intPackageCount int,
booGood bit,
txtShotID char(10),
txtStatus char(10),
qvTime decimal(16, 6)
)
END
ELSE
BEGIN
print '0 records in ' + @.tableName
END
EXEC sp_xml_removedocument @.docHandle
/**
--undesired unicode results: 1) it's unicode and 2) the data is blank after
the first number is translated
0x310030003900340036003500330039003600340030003000 300030003000
...
0x310030003900340036003500330034003200310030003000 300030003000
...
--normal result using vb
0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
**/
The XML is Unicode once it is parsed. If you want to get it into
non-Unicode, change

> binData varbinary(2000),
to

> binData varchar(8000),
in your WITH clause.
HTH
Michael
"TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
> /***
> Requirement: Insert ascii text into varbinary field allowing SQL to
> convert
> the data implicitly
> Problem: The data is encoded as Unicode and is truncated or simply
> blanked
> out after the first 10 characters
> The following is valid and works; however, it doesn't solve my problem.
> As a result, I've tried several approaches, including creating a cursor
> and
> calling Cast() to convert the data to binary. This appears to work, but
> again, stops after the first 10 characters
> **/
> DECLARE @.docHandle int
> DECLARE @.xmlDoc varchar(5000)
> SET @.xmlDoc ='
> <?xml version="1.0"?>
> <f2dbarchive>
> <archive_timestamp datetime="2004-Sep-27 12:28:48 AM" />
> <archive_test id="1530" />
> <!-- tables used in this archive -->
> <tables>
> <Row txtTablename="tblFinalData" txtKeyName="lngInstanceID"
> lngKeyValue="2096" lngRecordCount="4" />
> </tables>
> <!-- data from the tables -->
> <data>
> <tblFinalData>
> <Row lngDataID="5336" lngInstanceID="2096"
> binData="10946534210000000000276
> Final
> 35031.16534415632448.030617561162.062056640780.437 87345288720.529611595975413.8801526137533849.47590 9519025.77227602431221.58205664078257.545
> 0.085 DUST SPOTTER 3 LOF 85.00
> NY3849.475909519025.7722760243122.0620566407829311 98.67
> 32086.86
> 160 85 31198.67 32086.86
> 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B SOUTH PED
> 123.21566117 -0.30555699 0.0010055 0.00555699
> 162.7038151636657.46 2017 Y Y34642.169 33069.594
> 172.887 MND 5.5B_NEW
> 147.95450669 -0.39920593 0.02882665 0.34920593
> 167.814226706570
> 2019 N N31198.912 32050.403 140.93
> GP
> 8 OBS PT 0 0
> -0.000382 0.01695564 0 0 1658
> Y
> Y35282.726 33023.024 175.67 TOWER @. 6B SOUTH PED
> 203.64517087 -1.08627593 0.00482913 0.03627593
> 163.7961106000857.63 2015 N N31892.772 32960.261
> 153.253 OBS PT 3B SOUTH 0
> 0 0.00531249 0.11706955 0 0
> "
> intPackageCount="" booGood="1" txtShotID="" txtStatus="Field" qvTime="0"
> />
> <Row lngDataID="5337" lngInstanceID="2096"
> binData="10946539640000000000277
> Final
> 34900.43868810932427.234930849677.0883333717911.85 11700763559.4096332145782681.194252534983717.38436 4552516.449301983071536.608333371790
> 0 1ST LIGHT SPOTTER 3
> NY3717.384364552516.449301983071517.08833337179311 98.67
> 32086.86 160 85 31198.67
> 32086.86 140.48 85 0 0 1656
> Y Y34149.809 33025.014 168.247 TOWER @. 5B
> SOUTH
> PED 128.4489945 34.57777634 0.0010055
> 0.00555699 829.755965865400 2017 Y Y34642.169
> 33069.594 172.887 MND 5.5B_NEW
> 158.30450669 44.65079407 0.02882665 0.34920593
> 856.898004060910 2019 N N31198.912 32050.403
> 140.93 GP 8 OBS PT 0
> 0 -0.000382 0.01695564 0 0
> 1658
> N N35282.726 33023.024 175.67 TOWER @. 6B
> SOUTH PED 0 0
> 0.00482913 0.03627593 0 0 2015
> Y
> Y31892.772 32960.261 153.253 OBS PT 3B SOUTH
> 100.09468751 12.76626379 0.00531249 0.11706955
> 845.995612034910
> " intPackageCount="" booGood="1" txtShotID=""
> txtStatus="Field" qvTime="0" />
> </tblFinalData>
> </data>
> </f2dbarchive>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDoc
> DECLARE @.tableName varchar(80)
> DECLARE @.instID int
> SET @.instID=2096
> SET @.tableName='tblFinalData'
> IF (SELECT lngRecordCount FROM OPENXML(@.docHandle,
> '/f2dbarchive/tables/Row', 1)
> WITH (txtTablename varchar(80), lngRecordCount int)
> WHERE txtTablename=@.tableName) > 0
> BEGIN
> print 'at least 1 row in '+@.tableName
> INSERT INTO tblFinalData
> (lngInstanceID, binData, intPackageCount, booGood, txtShotID, txtStatus,
> qvTime)
> SELECT @.instID, cast(binData as varbinary) as binData, intPackageCount,
> booGood, txtShotID, txtStatus, qvTime
> FROM OpenXML(@.docHandle, '/f2dbarchive/data/tblFinalData/Row', 1)
> WITH (
> lngInstanceID int,
> binData varbinary(2000),
> intPackageCount int,
> booGood bit,
> txtShotID char(10),
> txtStatus char(10),
> qvTime decimal(16, 6)
> )
> END
> ELSE
> BEGIN
> print '0 records in ' + @.tableName
> END
>
> EXEC sp_xml_removedocument @.docHandle
> /**
> --undesired unicode results: 1) it's unicode and 2) the data is blank
> after
> the first number is translated
> 0x310030003900340036003500330039003600340030003000 300030003000
> ...
> 0x310030003900340036003500330034003200310030003000 300030003000
> ...
> --normal result using vb
> 0x313039343635353933393030303030303030303032373920 20202020202046696E616C2020202020333531...
> **/
|||Thanks...that's great...gets me past creating a cursor. If I may be so dense
to ask one more issue:
After processing, this is all I get:
0x313039343635333432313030303030303030303032373620 202020202020
From a source string of:
10946534210000000000276 Final
35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
actually about 1600 characters wide}
Is there something wrong with mixed numeric/text?
Any followup is greatly appreciated.
Dave
"Michael Rys [MSFT]" wrote:

> The XML is Unicode once it is parsed. If you want to get it into
> non-Unicode, change
>
> to
>
> in your WITH clause.
> HTH
> Michael
> "TeraComp in Florida" <TeraComp in Florida@.discussions.microsoft.com> wrote
> in message news:3F1475DE-B508-4F80-B7DF-68C40E627FAD@.microsoft.com...
>
>
|||I've discovered that cast(binData as binary(2000)) properly stores the data.
My guess is SQL Sever see the space as a field terminator and stops
processing.
If this is right, the follow-on question should be, how do I force SQL to
read the entire data word?
Or am I stuck with fixed width binary data? (incidently, the reason for
using a varbinary column was for efficiency)
Thanks again...I'm feeling close to resolving my delimma.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> Thanks...that's great...gets me past creating a cursor. If I may be so dense
> to ask one more issue:
> After processing, this is all I get:
> 0x313039343635333432313030303030303030303032373620 202020202020
> From a source string of:
> 10946534210000000000276 Final
> 35031.16534415632448.030617561162.062056640780.437 8734528 {the string is
> actually about 1600 characters wide}
> Is there something wrong with mixed numeric/text?
> Any followup is greatly appreciated.
> Dave
> "Michael Rys [MSFT]" wrote:
|||How do you check? If you use the query analyzer, did you set the column
width large enough?
The space should not be treated as a field terminator...
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the
> data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Found the bug:
cast(binData as varbinary) as binData
Should be
cast(binData as varbinary(2000)) as binData
SQL Server was kind enough to all the former, but this allow it to determine
the length of the varBinary data.
Many thanks to Michael Rys for expert guidance.
Dave
"TeraComp in Florida" wrote:
[vbcol=seagreen]
> I've discovered that cast(binData as binary(2000)) properly stores the data.
> My guess is SQL Sever see the space as a field terminator and stops
> processing.
> If this is right, the follow-on question should be, how do I force SQL to
> read the entire data word?
> Or am I stuck with fixed width binary data? (incidently, the reason for
> using a varbinary column was for efficiency)
> Thanks again...I'm feeling close to resolving my delimma.
> Dave
> "TeraComp in Florida" wrote:
|||Yes I use QA using select len(bindata) as ldata, bindata from tblFinalData
That eventually led me to discover the need for cast(binData as
varbinary(2000))
which solves my problem.
It would be noteworthy to discover why SQL Server stopped parsing the text
Try this:
Sql#1: select cast('1094653421000000000027635031.16534415632448. 03061756'
as varbinary) as tdata
Sql#2: select cast('1094653421000000000027635031.16534415632448. 03061756' as
varbinary(100)) as tdata
The results I get are
#1 is truncated to 30 characters (doesn't matter what characters)
#2 is complete and variable as expected
Therefore: (with my Florida hurricane-ridden mind), SQL Server's cast
function defaults to 30-characters. [guess I missed that in the Books-Online]
Dave
cast('somedata' as
"Michael Rys [MSFT]" wrote:

> How do you check? If you use the query analyzer, did you set the column
> width large enough?
> The space should not be treated as a field terminator...
> Best regards
> Michael
> "TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
> message news:FD29C515-44BF-4A4A-A61B-0290738F0AB8@.microsoft.com...
>
>
|||Hmm. I should have seen this (mea culpa) as well.
In SQL Server 2005, you probably want to use varbinary(max) instead :-).
Best regards
Michael
"TeraComp in Florida" <TeraCompinFlorida@.discussions.microsoft.com> wrote in
message news:2233608D-86CD-4EF3-AFCE-E22C274A8216@.microsoft.com...[vbcol=seagreen]
> Found the bug:
> cast(binData as varbinary) as binData
> Should be
> cast(binData as varbinary(2000)) as binData
> SQL Server was kind enough to all the former, but this allow it to
> determine
> the length of the varBinary data.
> Many thanks to Michael Rys for expert guidance.
> Dave
>
> "TeraComp in Florida" wrote: