May 27, 2008 at 11:29 am
If this is the wrong forum for this post I apologize and please move to the correct forum.
We are trying to place XML documents inside SQL Server 2005 in an XML datatype. Part of the process is to migrate documents into SQL Server. We have encountered an issue where the convert functionality is producing the following error:
"An error occurred while executing batch. Error message is: '.', hexadecimal value 0x00, is an invalid character."
This is produced when converting from a varchar to XML with a style of '1'. We need to preserve whitespace in these documents for formatting reasons.
However, and this is the weird part, simply adding a character to the string and the it converts fine.
Here is the SQL statement we have ran in the managment studio (this one works):
selectconvert(xml,'
ab
(n) Prepare proposals and advise on the investment of
idle local government funds;
(o) Administer the program of grants, loans and loan
guarantees under the federal Public Works and Economic
Development Act of 1965, as amended, 42 U.S.C. 3121 et seq.,
and to receive and disburse State and federal funds provided
for that program and moneys received as repayments of loans
made under the program;
(p) After January 1, 1985, upon the request of local
governments, to prepare and provide model financial statement
forms designed to communicate to taxpayers, service
consumers, voters, government employees and news media, in a
non-technical manner, all significant financial information
regarding a particular local government, and to prepare and
provide to local governments a summary of local governments''
obligations concerning the adoption of an annual operating
budget which summary shall be set forth in a non-technical
manner and shall be designed principally for distribution to,
and the use of, taxpayers, service consumers, voters,
government employees and news media.
(Source: P.A. 83-1362.)
Section 15. The Center for Business Ownership Succession
and Employee Ownership Act is amended by changing Section 2
as follows:
(20 ILCS 609/2)
Sec. 2. Center for Business Ownership Succession and
Employee Ownership.
(a) There is created within the Department of Commerce
and Community Affairs the Center for Business Ownership
Succession and Employee Ownership.
The purpose of the Center is to foster greater awareness
of the most effective techniques that facilitate business
ownership succession and employee ownership with an emphasis
on the retention and creation of job opportunities.
(b) The Center shall have the authority to do the
following:
(1) Develop and disseminate materials to promote
effective business ownership succession and employee
ownership strategies.
(2) Provide counseling to individual companies and
referral services to provide professional advisors expert
in the field of business ownership succession and
employee ownership.
(3) Plan, organize, sponsor, or conduct conferences
and workshops on business ownership succession and
employee ownership issues.
(4) Network and contract with local economic
development agencies, business organizations, and
professional advisors to accomplish the goals of the
Center.
(5) Raise money from private sources to support the
work of the Center.
(c)
.
(Source: P.A. 89-364, eff. 8-18-95.)
Section 20. The Illinois Coal and Energy Development
Bond Act is amended by changing Section 6 as follows:
(20 ILCS 1110/6) (from Ch. 96 1/2, par. 4106)
Sec. 6. The Department of Commerce and Community Affairs
is authorized to use $120,000,000 for the purposes specified
in this Act. These funds shall be expended only
for a grant to the owner of a
generating station located in Illinois and having at least
three coal-fired generating units with accredited summer
capacity greater than 500 megawatts each at such generating
station as specifically authorized by this paragraph.
Notwithstanding any of the other provisions of this Act, in
considering the approval of projects to be funded under this
Act, the Department of Commerce and Community Affairs
shall give special
consideration to projects which are designed to remove sulfur
and other pollutants in the preparation and utilization of
coal, and in the use and operation of electric utility
generating plants and industrial facilities which utilize
Illinois coal as their primary source of fuel. The
Department of Commerce and Community Affairs is directed to
enter into a contract with the owner of a generating station
located in Illinois and having at least three coal-fired
generating units with accredited summer capability greater
than 500 megawatts each at such generating station for a
grant of $35,000,000 to be made by the State of Illinois to
such owner to be used to pay costs of designing, acquiring,
constructing, installing and testing facilities to reduce
sulfur dioxide emissions at one such generating unit to allow
that unit to meet the requirements of the Federal Clean Air
Act Amendments of 1990 (P.L. 101-549) while continuing to use
coal mined in Illinois as its source of fuel.
(Source: P.A. 89-445, eff. 2-7-96.)
Section 25. The Disabled Persons Rehabilitation Act is
amended by changing Section 3 as follows:
(20 ILCS 2405/3) (from Ch. 23, par. 3434)
Sec. 3. Powers and duties. The Department shall have the
powers and duties enumerated herein:
(a) To co-operate with the federal government in the
administration of the provisions of the federal
Rehabilitation Act of 1973, as amended, and of the federal
Social Security Act to the extent and in the manner provided
in these Acts.
(b) To prescribe and supervise such courses of
vocational training and provide such other services as may be
necessary for the habilitation and rehabilitation of persons
with one or more disabilities, including the administrative
activities under subsection (e) of this Section, and to
co-operate with State and local school authorities and other
recognized agencies engaged in habilitation, rehabilitation
and comprehensive rehabilitation services; and to cooperate
with the Department of Children and Family Services regarding
the care and education of children with one or more
disabilities.
(c) To make such reports and submit such plans to the
federal government as are required by the provisions of the
federal Rehabilitation Act of 1973, as amended, and by the
rules and regulations of the federal agency or agencies
administering the federal Rehabilitation Act of 1973, as
amended, and the federal Social Security Act.
(d) To report in writing, to the Governor, annually on
or before the first day of December, and at such other times
and in such manner and upon such subjects as the Governor may
require. The annual report shall contain (1) a statement of
the existing condition of comprehensive rehabilitation
services, habilitation and rehabilitation in the State; (2) a
statement of suggestions and recommendations with reference
to the development of comprehensive rehabilitation services,
habilitation and rehabilitation in the State; and (3) an
itemized statement of the amounts of money received from
federal, State and other sources, and of the objects and
purposes to which the respective items of these several
amounts have been devoted.
(e) To exercise, pursuant to Section 13 of this Act,
executive and administrative supervision over all
institutions, divisions, programs and services now existing
or hereafter acquired or created under the jurisdiction of
the Department, including, but not limited to, the following:
The Illinois School for the Visually Impaired at
Jacksonville, as provided under Section 10 of this Act,
The Illinois School for the Deaf at Jacksonville, as
provided under Section 10 of this Act, and
The Illinois Center for Rehabilitation and Education, as
provided under Section 11 of this Act.
(f) To establish a program of services to prevent
unnecessary institutional
',1)
Here is the exact same SQL but this time we removed an 's' after title...this fails:
selectconvert(xml,'
a
(n) Prepare proposals and advise on the investment of
idle local government funds;
(o) Administer the program of grants, loans and loan
guarantees under the federal Public Works and Economic
Development Act of 1965, as amended, 42 U.S.C. 3121 et seq.,
and to receive and disburse State and federal funds provided
for that program and moneys received as repayments of loans
made under the program;
(p) After January 1, 1985, upon the request of local
governments, to prepare and provide model financial statement
forms designed to communicate to taxpayers, service
consumers, voters, government employees and news media, in a
non-technical manner, all significant financial information
regarding a particular local government, and to prepare and
provide to local governments a summary of local governments''
obligations concerning the adoption of an annual operating
budget which summary shall be set forth in a non-technical
manner and shall be designed principally for distribution to,
and the use of, taxpayers, service consumers, voters,
government employees and news media.
(Source: P.A. 83-1362.)
Section 15. The Center for Business Ownership Succession
and Employee Ownership Act is amended by changing Section 2
as follows:
(20 ILCS 609/2)
Sec. 2. Center for Business Ownership Succession and
Employee Ownership.
(a) There is created within the Department of Commerce
and Community Affairs the Center for Business Ownership
Succession and Employee Ownership.
The purpose of the Center is to foster greater awareness
of the most effective techniques that facilitate business
ownership succession and employee ownership with an emphasis
on the retention and creation of job opportunities.
(b) The Center shall have the authority to do the
following:
(1) Develop and disseminate materials to promote
effective business ownership succession and employee
ownership strategies.
(2) Provide counseling to individual companies and
referral services to provide professional advisors expert
in the field of business ownership succession and
employee ownership.
(3) Plan, organize, sponsor, or conduct conferences
and workshops on business ownership succession and
employee ownership issues.
(4) Network and contract with local economic
development agencies, business organizations, and
professional advisors to accomplish the goals of the
Center.
(5) Raise money from private sources to support the
work of the Center.
(c)
.
(Source: P.A. 89-364, eff. 8-18-95.)
Section 20. The Illinois Coal and Energy Development
Bond Act is amended by changing Section 6 as follows:
(20 ILCS 1110/6) (from Ch. 96 1/2, par. 4106)
Sec. 6. The Department of Commerce and Community Affairs
is authorized to use $120,000,000 for the purposes specified
in this Act. These funds shall be expended only
for a grant to the owner of a
generating station located in Illinois and having at least
three coal-fired generating units with accredited summer
capacity greater than 500 megawatts each at such generating
station as specifically authorized by this paragraph.
Notwithstanding any of the other provisions of this Act, in
considering the approval of projects to be funded under this
Act, the Department of Commerce and Community Affairs
shall give special
consideration to projects which are designed to remove sulfur
and other pollutants in the preparation and utilization of
coal, and in the use and operation of electric utility
generating plants and industrial facilities which utilize
Illinois coal as their primary source of fuel. The
Department of Commerce and Community Affairs is directed to
enter into a contract with the owner of a generating station
located in Illinois and having at least three coal-fired
generating units with accredited summer capability greater
than 500 megawatts each at such generating station for a
grant of $35,000,000 to be made by the State of Illinois to
such owner to be used to pay costs of designing, acquiring,
constructing, installing and testing facilities to reduce
sulfur dioxide emissions at one such generating unit to allow
that unit to meet the requirements of the Federal Clean Air
Act Amendments of 1990 (P.L. 101-549) while continuing to use
coal mined in Illinois as its source of fuel.
(Source: P.A. 89-445, eff. 2-7-96.)
Section 25. The Disabled Persons Rehabilitation Act is
amended by changing Section 3 as follows:
(20 ILCS 2405/3) (from Ch. 23, par. 3434)
Sec. 3. Powers and duties. The Department shall have the
powers and duties enumerated herein:
(a) To co-operate with the federal government in the
administration of the provisions of the federal
Rehabilitation Act of 1973, as amended, and of the federal
Social Security Act to the extent and in the manner provided
in these Acts.
(b) To prescribe and supervise such courses of
vocational training and provide such other services as may be
necessary for the habilitation and rehabilitation of persons
with one or more disabilities, including the administrative
activities under subsection (e) of this Section, and to
co-operate with State and local school authorities and other
recognized agencies engaged in habilitation, rehabilitation
and comprehensive rehabilitation services; and to cooperate
with the Department of Children and Family Services regarding
the care and education of children with one or more
disabilities.
(c) To make such reports and submit such plans to the
federal government as are required by the provisions of the
federal Rehabilitation Act of 1973, as amended, and by the
rules and regulations of the federal agency or agencies
administering the federal Rehabilitation Act of 1973, as
amended, and the federal Social Security Act.
(d) To report in writing, to the Governor, annually on
or before the first day of December, and at such other times
and in such manner and upon such subjects as the Governor may
require. The annual report shall contain (1) a statement of
the existing condition of comprehensive rehabilitation
services, habilitation and rehabilitation in the State; (2) a
statement of suggestions and recommendations with reference
to the development of comprehensive rehabilitation services,
habilitation and rehabilitation in the State; and (3) an
itemized statement of the amounts of money received from
federal, State and other sources, and of the objects and
purposes to which the respective items of these several
amounts have been devoted.
(e) To exercise, pursuant to Section 13 of this Act,
executive and administrative supervision over all
institutions, divisions, programs and services now existing
or hereafter acquired or created under the jurisdiction of
the Department, including, but not limited to, the following:
The Illinois School for the Visually Impaired at
Jacksonville, as provided under Section 10 of this Act,
The Illinois School for the Deaf at Jacksonville, as
provided under Section 10 of this Act, and
The Illinois Center for Rehabilitation and Education, as
provided under Section 11 of this Act.
(f) To establish a program of services to prevent
unnecessary institutional
',1)
As anyone else ran into this issue, we are SQL Server 2005 Microsoft SQL Server Management Studio9.00.3042.00, Operating System6.0.6000
-Thanks.
May 27, 2008 at 12:13 pm
We cannot read this very well, the tags all got eaten by the BB software. Could you please re-edit it with the editor at this link:http://extras.sqlservercentral.com/prettifier/prettifier.aspx and re-post it?
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 12:34 pm
The XML is not playing nice in prettifier, it is not able to nicely format the code.
Is there another way I could post the example in a text file?
May 27, 2008 at 12:38 pm
oops, almost forgot: attach it as a separate file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 12:44 pm
If I did it correctly attached is the text file with the XML and the convert function.
May 27, 2008 at 1:12 pm
OK, I have it, tried it and get the same error.
This appears to be a simple length limitation in XML. removing any one text character makes the error go away (not just whitespace). I suspect that this is a bug in the "preserve whitespace" XML converter.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 1:17 pm
Thats what we are thinking as well. It is not a length limitation in that we have other documents around 4mb in size that converted with no issue. We have at this point converted 360,000 documents and have encountered three which produce the error.
However we noticed that using the style '0', not to preserve whitespace saves the document into SQL Server with no problem.
We have called Microsoft and I will post their reply.
-Thanks for confirming the error.
May 27, 2008 at 1:45 pm
Yes, 0 & 2 both work; 1 & 3 both fail.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2008 at 11:06 am
Contacted Microsoft and they were able to reproduce the error (thankfully) and we should know in the next day or two what they suggest we do. It is an issue with the convert function for XML maintaining whitespace.
Hopefully the reply is not 'It is fixed in 2008'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply