Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.
Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>
|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>
|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegrou ps.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>
Showing posts with label character. Show all posts
Showing posts with label character. Show all posts
Monday, March 26, 2012
Image to Varchar
Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>sql
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>sql
Image to Varchar
Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:
> > But, here the image is too lengthy.
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> > Hi All,
> > I have a small doubt.
> > How to convert an Imaage of about 110 character length to varchar.
> > The image is nothing but the text data stored in that format.
> > While using "convert" function for smaller data,Image to Varchar and
> > Varchar to Image conversion is successful.
> > But, here the image is too lengthy.
> > Kindly, anyone suggest a way.
> > Thanks,
> > Dutt.
> >|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>> > But, here the image is too lengthy.
>> What do you mean by 'too lengthy'?
>> Try:
>> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
>> FROM dbo.MyTable
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
>> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
>> > Hi All,
>> > I have a small doubt.
>> > How to convert an Imaage of about 110 character length to varchar.
>> > The image is nothing but the text data stored in that format.
>> > While using "convert" function for smaller data,Image to Varchar and
>> > Varchar to Image conversion is successful.
>> > But, here the image is too lengthy.
>> > Kindly, anyone suggest a way.
>> > Thanks,
>> > Dutt.
>> >
>
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:
> > But, here the image is too lengthy.
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> > Hi All,
> > I have a small doubt.
> > How to convert an Imaage of about 110 character length to varchar.
> > The image is nothing but the text data stored in that format.
> > While using "convert" function for smaller data,Image to Varchar and
> > Varchar to Image conversion is successful.
> > But, here the image is too lengthy.
> > Kindly, anyone suggest a way.
> > Thanks,
> > Dutt.
> >|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>> > But, here the image is too lengthy.
>> What do you mean by 'too lengthy'?
>> Try:
>> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
>> FROM dbo.MyTable
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
>> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
>> > Hi All,
>> > I have a small doubt.
>> > How to convert an Imaage of about 110 character length to varchar.
>> > The image is nothing but the text data stored in that format.
>> > While using "convert" function for smaller data,Image to Varchar and
>> > Varchar to Image conversion is successful.
>> > But, here the image is too lengthy.
>> > Kindly, anyone suggest a way.
>> > Thanks,
>> > Dutt.
>> >
>
Friday, March 23, 2012
Image data type to character string
I have a table with an image datatype field.
When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?
Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..
If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.
Wednesday, March 21, 2012
Image data type to character string
I have a table with an image datatype field.
When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?
Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..
If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.
Monday, March 12, 2012
Illegal xml character error during Bulk Load
Hello,
I am receiving the error 'Illegal xml character' a Bulk Load insert using
SQLXML 3.0
Has anyone else received this error? Any help would be greatly appreciated.
Thanks!
Sean
This is an XML parser error. It means that you most likely have a byte code
in your data stream that XML does not allow (most likely in the range hex(0)
to hex(31) where only TAB, CR and LF are allowed).
In order to fix it, you need to clean your data.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
> appreciated.
> Thanks!
> Sean
>
|||Your input Xml file could be an invalid Xml file. Try to fix the errors
before you pass it to Bulkload. You may use IE to check if the Xml file is
valid by simply loading it.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>
|||Thanks for the quick reply.
Sorry for such a newbie question, but how do I begin to clean this data?
The file is a 500 MB XML document and I'm not sure how to clean out these
types of characters. Any suggestions?
Thanks Michael,
Sean
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
> This is an XML parser error. It means that you most likely have a byte
> code in your data stream that XML does not allow (most likely in the range
> hex(0) to hex(31) where only TAB, CR and LF are allowed).
> In order to fix it, you need to clean your data.
> Best regards
> Michael
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
>
|||Bertran,
I've tried opening in IE, but the page times out after 20 mins. The file is
about 500 MB. I have a total of 3 XML files that need importing and I have
had success importing the smallest of these files (500 & 5000 KB). It's
the big one that errors out almost immediately.
The task continues to be searching for the illegal XML syntax...
Sean
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Your input Xml file could be an invalid Xml file. Try to fix the errors
> before you pass it to Bulkload. You may use IE to check if the Xml file is
> valid by simply loading it.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> appreciated.
>
|||The easiest is to run a script that basically streams over the 500MB and
removes all offending characters.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:ux8QSuZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Thanks for the quick reply.
> Sorry for such a newbie question, but how do I begin to clean this data?
> The file is a 500 MB XML document and I'm not sure how to clean out these
> types of characters. Any suggestions?
> Thanks Michael,
> Sean
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
>
|||Try xmlspy, the home edition is free and it will validate your file assuming
it can load it, it's worth a try
john
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:OFxliIapEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Bertran,
> I've tried opening in IE, but the page times out after 20 mins. The file
> is about 500 MB. I have a total of 3 XML files that need importing and I
> have had success importing the smallest of these files (500 & 5000 KB).
> It's the big one that errors out almost immediately.
> The task continues to be searching for the illegal XML syntax...
> Sean
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
>
|||There is probably an illegal xml character in your data file that you are
uploading to the server.
- Chandra
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>
I am receiving the error 'Illegal xml character' a Bulk Load insert using
SQLXML 3.0
Has anyone else received this error? Any help would be greatly appreciated.
Thanks!
Sean
This is an XML parser error. It means that you most likely have a byte code
in your data stream that XML does not allow (most likely in the range hex(0)
to hex(31) where only TAB, CR and LF are allowed).
In order to fix it, you need to clean your data.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
> appreciated.
> Thanks!
> Sean
>
|||Your input Xml file could be an invalid Xml file. Try to fix the errors
before you pass it to Bulkload. You may use IE to check if the Xml file is
valid by simply loading it.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>
|||Thanks for the quick reply.
Sorry for such a newbie question, but how do I begin to clean this data?
The file is a 500 MB XML document and I'm not sure how to clean out these
types of characters. Any suggestions?
Thanks Michael,
Sean
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
> This is an XML parser error. It means that you most likely have a byte
> code in your data stream that XML does not allow (most likely in the range
> hex(0) to hex(31) where only TAB, CR and LF are allowed).
> In order to fix it, you need to clean your data.
> Best regards
> Michael
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
>
|||Bertran,
I've tried opening in IE, but the page times out after 20 mins. The file is
about 500 MB. I have a total of 3 XML files that need importing and I have
had success importing the smallest of these files (500 & 5000 KB). It's
the big one that errors out almost immediately.
The task continues to be searching for the illegal XML syntax...
Sean
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Your input Xml file could be an invalid Xml file. Try to fix the errors
> before you pass it to Bulkload. You may use IE to check if the Xml file is
> valid by simply loading it.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> appreciated.
>
|||The easiest is to run a script that basically streams over the 500MB and
removes all offending characters.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:ux8QSuZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Thanks for the quick reply.
> Sorry for such a newbie question, but how do I begin to clean this data?
> The file is a 500 MB XML document and I'm not sure how to clean out these
> types of characters. Any suggestions?
> Thanks Michael,
> Sean
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
>
|||Try xmlspy, the home edition is free and it will validate your file assuming
it can load it, it's worth a try
john
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:OFxliIapEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Bertran,
> I've tried opening in IE, but the page times out after 20 mins. The file
> is about 500 MB. I have a total of 3 XML files that need importing and I
> have had success importing the smallest of these files (500 & 5000 KB).
> It's the big one that errors out almost immediately.
> The task continues to be searching for the illegal XML syntax...
> Sean
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
>
|||There is probably an illegal xml character in your data file that you are
uploading to the server.
- Chandra
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>
illegal xml character
I got this from the w3:
[2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
[#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
I'm not much of a hex guy, is there anyway in SQL to
figure out which characters there are, basically someway I
can create a script that will parse out the bad characters?
Regards,
Bryan
>--Original Message--
>Alternatively what I want to do is just select out the
>records I do need and cast them as xml. Do I still have
>the same operators I would if I had a native XML
datatype.
>For instance:
>select top 10
> cast(bidData as xml) as bidData.query
>(X_NBTA05/PB/PROPCODE)
>from
> tblBidData bid
>inner join
> tblRFPProgram rfp
>on
> rfp.ProgName = bid.CorpRFPNumber
>where
> ProgTypeID = 2
>
>I get:
>Msg 102, Level 15, State 1, Line 5
>Incorrect syntax near '.'.
>
character
>.
>You means something like:
declare @.x nvarchar(50)
set @.x = CAST (0x3000310032004000070020003000 as nvarchar(50))
WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST(0x0100 as nvarchar(1))
AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
BEGIN
SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
SELECT @.x
END
IF LEN(@.x) > 0 SELECT 'Error'
?
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
>I got this from the w3:
> [2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
> [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
> http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
> I'm not much of a hex guy, is there anyway in SQL to
> figure out which characters there are, basically someway I
> can create a script that will parse out the bad characters?
> Regards,
> Bryan
>
> datatype.
> character|||Michael,
You never seize to amaze me. Thank you.
Bryan
>--Original Message--
>You means something like:
>declare @.x nvarchar(50)
>set @.x = CAST (0x3000310032004000070020003000 as nvarchar
(50))
>WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST
(0x0100 as nvarchar(1))
>AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
>BEGIN
>SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
>SELECT @.x
>END
>IF LEN(@.x) > 0 SELECT 'Error'
>
>?
>
>HTH
>Michael
><anonymous@.discussions.microsoft.com> wrote in message
>news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
someway I
characters?
what
>
>.
>
[2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
[#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
I'm not much of a hex guy, is there anyway in SQL to
figure out which characters there are, basically someway I
can create a script that will parse out the bad characters?
Regards,
Bryan
>--Original Message--
>Alternatively what I want to do is just select out the
>records I do need and cast them as xml. Do I still have
>the same operators I would if I had a native XML
datatype.
>For instance:
>select top 10
> cast(bidData as xml) as bidData.query
>(X_NBTA05/PB/PROPCODE)
>from
> tblBidData bid
>inner join
> tblRFPProgram rfp
>on
> rfp.ProgName = bid.CorpRFPNumber
>where
> ProgTypeID = 2
>
>I get:
>Msg 102, Level 15, State 1, Line 5
>Incorrect syntax near '.'.
>
character
>.
>You means something like:
declare @.x nvarchar(50)
set @.x = CAST (0x3000310032004000070020003000 as nvarchar(50))
WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST(0x0100 as nvarchar(1))
AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
BEGIN
SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
SELECT @.x
END
IF LEN(@.x) > 0 SELECT 'Error'
?
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
>I got this from the w3:
> [2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
> [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
> http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
> I'm not much of a hex guy, is there anyway in SQL to
> figure out which characters there are, basically someway I
> can create a script that will parse out the bad characters?
> Regards,
> Bryan
>
> datatype.
> character|||Michael,
You never seize to amaze me. Thank you.
Bryan
>--Original Message--
>You means something like:
>declare @.x nvarchar(50)
>set @.x = CAST (0x3000310032004000070020003000 as nvarchar
(50))
>WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST
(0x0100 as nvarchar(1))
>AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
>BEGIN
>SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
>SELECT @.x
>END
>IF LEN(@.x) > 0 SELECT 'Error'
>
>?
>
>HTH
>Michael
><anonymous@.discussions.microsoft.com> wrote in message
>news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
someway I
characters?
what
>
>.
>
illegal xml character
I got this from the w3:
[2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
[#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
I'm not much of a hex guy, is there anyway in SQL to
figure out which characters there are, basically someway I
can create a script that will parse out the bad characters?
Regards,
Bryan
>--Original Message--
>Alternatively what I want to do is just select out the
>records I do need and cast them as xml. Do I still have
>the same operators I would if I had a native XML
datatype.[vbcol=seagreen]
>For instance:
>select top 10
>cast(bidData as xml) as bidData.query
>(X_NBTA05/PB/PROPCODE)
>from
>tblBidData bid
>inner join
>tblRFPProgram rfp
>on
>rfp.ProgName = bid.CorpRFPNumber
>where
>ProgTypeID = 2
>
>I get:
>Msg 102, Level 15, State 1, Line 5
>Incorrect syntax near '.'.
>
character
>.
>
You means something like:
declare @.x nvarchar(50)
set @.x = CAST (0x3000310032004000070020003000 as nvarchar(50))
WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST(0x0100 as nvarchar(1))
AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
BEGIN
SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
SELECT @.x
END
IF LEN(@.x) > 0 SELECT 'Error'
?
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...[vbcol=seagreen]
>I got this from the w3:
> [2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
> [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
> http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
> I'm not much of a hex guy, is there anyway in SQL to
> figure out which characters there are, basically someway I
> can create a script that will parse out the bad characters?
> Regards,
> Bryan
> datatype.
> character
|||Michael,
You never seize to amaze me. Thank you.
Bryan
>--Original Message--
>You means something like:
>declare @.x nvarchar(50)
>set @.x = CAST (0x3000310032004000070020003000 as nvarchar
(50))
>WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST
(0x0100 as nvarchar(1))[vbcol=seagreen]
>AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
>BEGIN
>SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
>SELECT @.x
>END
>IF LEN(@.x) > 0 SELECT 'Error'
>
>?
>
>HTH
>Michael
><anonymous@.discussions.microsoft.com> wrote in message
>news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
someway I[vbcol=seagreen]
characters?[vbcol=seagreen]
what
>
>.
>
[2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
[#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
I'm not much of a hex guy, is there anyway in SQL to
figure out which characters there are, basically someway I
can create a script that will parse out the bad characters?
Regards,
Bryan
>--Original Message--
>Alternatively what I want to do is just select out the
>records I do need and cast them as xml. Do I still have
>the same operators I would if I had a native XML
datatype.[vbcol=seagreen]
>For instance:
>select top 10
>cast(bidData as xml) as bidData.query
>(X_NBTA05/PB/PROPCODE)
>from
>tblBidData bid
>inner join
>tblRFPProgram rfp
>on
>rfp.ProgName = bid.CorpRFPNumber
>where
>ProgTypeID = 2
>
>I get:
>Msg 102, Level 15, State 1, Line 5
>Incorrect syntax near '.'.
>
character
>.
>
You means something like:
declare @.x nvarchar(50)
set @.x = CAST (0x3000310032004000070020003000 as nvarchar(50))
WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST(0x0100 as nvarchar(1))
AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
BEGIN
SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
SELECT @.x
END
IF LEN(@.x) > 0 SELECT 'Error'
?
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...[vbcol=seagreen]
>I got this from the w3:
> [2a] RestrictedChar ::= [#x1-#x8] | [#xB-#xC] |
> [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]
> http://www.w3.org/TR/2004/REC-xml11-20040204/#NT-Char
> I'm not much of a hex guy, is there anyway in SQL to
> figure out which characters there are, basically someway I
> can create a script that will parse out the bad characters?
> Regards,
> Bryan
> datatype.
> character
|||Michael,
You never seize to amaze me. Thank you.
Bryan
>--Original Message--
>You means something like:
>declare @.x nvarchar(50)
>set @.x = CAST (0x3000310032004000070020003000 as nvarchar
(50))
>WHILE LEN(@.x) > 0 AND NOT(SUBSTRING(@.x, 1, 1) > CAST
(0x0100 as nvarchar(1))[vbcol=seagreen]
>AND SUBSTRING(@.x, 1, 1) < CAST(0x0800 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0B00 as nvarchar(1))
>OR SUBSTRING(@.x, 1, 1) = CAST(0x0C00 as nvarchar(1)))
>BEGIN
>SELECT @.x = SUBSTRING(@.x,2,LEN(@.x))
>SELECT @.x
>END
>IF LEN(@.x) > 0 SELECT 'Error'
>
>?
>
>HTH
>Michael
><anonymous@.discussions.microsoft.com> wrote in message
>news:0cf801c4ed45$7623f1d0$a501280a@.phx.gbl...
someway I[vbcol=seagreen]
characters?[vbcol=seagreen]
what
>
>.
>
Subscribe to:
Posts (Atom)