August 26, 2004 at 2:31 pm
I have been searching for an answer to this for days and finally give up. I have an XML document that contains 2 fields. One of them is defined as binary in the document. I am trying to load this file into SQL somehow so that I can pass it into sp_xml_preparedocument. Does anyone know how I can do this?
All of the example I find have the document being created in the sql script and then off you go. I can't understand how I can get an external file like this into SQL Server so I can query it. I am sure it is right in front of my nose but I am at a loss.
Thanks, George
August 27, 2004 at 6:21 am
Look up BULK INSERT in Books Online. Make sure to read the section entitled "Copying Data From a Data File". About 3/4 of the way down that section it briefly discusses loading a image file. You first have to use bcp to create a format file that BULK INSERT can use.
Good luck.
August 27, 2004 at 9:29 am
Thanks very much for the reply. I can handle using Bulk Insert and a format file. But after thinking about this some more I think that whatever is in that field is actually encoded in bin.hex and would need to be "decoded" at some point.
The column that I am trying to update contains binary data in an SQL Server "image" column (the field currently begins with "0x"). So, I think I think my biggest problem is the conversion of this field either inside of SQL or outside somehow.
I am pretty sure the .xml file is loading from the OS using the stored procedure u_xmlfile_preparedocument written by Murray Crosswell. This will load and .xml file up to 14MB in size and pass it to sp_xml_preparedocument which I am then able to query using OPENXML.
Thanks again for your help. And if anyone has any insight on how to handle bin.hex data please let me know!
Thanks, George
August 30, 2004 at 12:05 am
I had a similar situation when I needed to transfer the binary data from one server to other. Finall, I used Access file to transfer the binary data and than load using OPENDATASOURCE functionality.
You may also try OPENROWSET
Hope that's a round about.
bm
bm21
August 30, 2004 at 9:19 am
Ok, I've got a little more clarity into this issue. The image field that I am trying to load from the xml file is in binhex format. A site that I found on the web said this:
"Columns of type BINARY, LONG BINARY, IMAGE, and VARBINARY are assumed to be base64-encoded format and are decoded automatically."
So, the code that I have written is most likely working fine. My problem seems to be decoding the binhex and enconding it in base64.
And bm, I think I am going to have to use .NET to do this. Was hoping for a pure SQL Server solution but after 3 full days of searching I can't find anything helpful.
Thanks bm and and mkeast for your replies. Still looking for help if someone stumbles across this message an comes up with an idea.
Thanks, George
August 30, 2004 at 9:46 am
Maybe the following VB code will help. You could try to implement the FromHex and Encode functions in T-SQL.
The RFC for BinHex: http://www.rfc-editor.org/rfc/rfc1741.txt
the RFC for Base64: http://www.rfc-editor.org/rfc/rfc2045.txt
Refer to this link: http://www.vb-helper.com/howto_encode_base64_hex.html
Contents of link are listed below:
Title
Encode and decode text to and from the BinHex and Base64 encodings
Description
This example shows how to encode and decode text to and from the BinHex and Base64 encodings in Visual Basic 6. It uses an XML DOM document to perform the conversions.
Thanks to James Hansen.
The main program displays a string in Base64 encoding and then decodes the encoding to verify that it contains the correct string. It then repeats those steps for the BinHex encoding.
To encode in Base 64, the program makes an XML document and gives it an encode element of type bin.base64. It copies the string into an array of bytes, assigns it to the encoder, and reads the result from its Text property.
To decode the Base 64, the program makes an XML string containing the encoded information, loads it, and reads the decoder node's text.
The functions that encode and decode BinHex work similarly.
Private Sub Form_Load()
Dim StringToEncode As String
StringToEncode = "Hi James!"
' To Base64
Debug.Print Encode(StringToEncode)
Debug.Print Decode(Encode(StringToEncode))
' To Hex
Debug.Print ToHex(StringToEncode)
Debug.Print FromHex(ToHex(StringToEncode))
End Sub
Public Function Encode(ByVal iStr As String) As String
Dim iXml As New MSXML2.DOMDocument30
Dim iArray() As Byte
With iXml.createElement("Encoder")
.dataType = "bin.base64"
ReDim iArray(LenB(iStr))
CopyMemory iArray(0), ByVal StrPtr(iStr), LenB(iStr)
.nodeTypedValue = iArray()
Encode = .Text
End With
End Function
Public Function Decode(ByVal iStrbase64 As String) As Byte()
Dim strXML As String
strXML = "<DECODER xmlns:dt=" & Chr(34) & _
"urn:schemas-microsoft-com:datatypes" & Chr(34) & " " & _
"dt:dt=" & Chr(34) & "bin.base64" & Chr(34) & ">" & _
iStrbase64 & "</DECODER>"
With New MSXML2.DOMDocument30
.loadXML strXML
Decode = .selectSingleNode("DECODER").nodeTypedValue
End With
End Function
Public Function ToHex(ByVal iStr As String) As String
Dim iXml As New MSXML2.DOMDocument30
Dim iArray() As Byte
With iXml.createElement("Encoder")
.dataType = "bin.hex"
ReDim iArray(LenB(iStr))
CopyMemory iArray(0), ByVal StrPtr(iStr), LenB(iStr)
.nodeTypedValue = iArray()
ToHex = .Text
End With
End Function
Public Function FromHex(ByVal iStrbase64 As String) As Byte()
Dim strXML As String
strXML = "<DECODER xmlns:dt=" & Chr(34) & _
"urn:schemas-microsoft-com:datatypes" & Chr(34) & " " & _
"dt:dt=" & Chr(34) & "bin.hex" & Chr(34) & ">" & _
iStrbase64 & "</DECODER>"
With New MSXML2.DOMDocument30
.loadXML strXML
FromHex = .selectSingleNode("DECODER").nodeTypedValue
End With
End Function
August 30, 2004 at 2:15 pm
mkeast,
Thanks for all of your help. I was thinking of trying what you have listed above. But I finally heard from the vendor that creates this xml file and the binary stuff is essentially a BLOB that has been zipped up. So the binary is actually in ZIP format. I am guessing that since this binary info is going into a column of type "image" that SQL Server is assuming this is base64 and converting.
Essentially it sounds like I need to get that binary information into the image column in the exact format it is in the xml file. So now I need to do an 180 degree turnaround! I need to get the contents of that xml element into the database with no conversion whatsoever. Jeez....
Thanks for your help. George
August 30, 2004 at 3:28 pm
Ahhh, sorry for the confusion. Ignore my last post. This silly thing *has* to be encoded somehow. If it were actually the "zip" source code there would be many invalid values imbedded in the element and that is not the case.
Thanks again for all of the help.
George
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply