SSDN - Invalid characters on relational data import

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 21 Feb 2006 05:37 PM

Hi,

I'm using SQL/XML to import data from a db, but one of the fields I'm importing has a lot of brackets (>,<) and some random unicode characters from html and emails entered in to it. This causes invalid XML. What's the best way to get the data in correctly?

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 21 Feb 2006 10:30 PM
Originally Posted: 21 Feb 2006 10:26 PM

(I did work it out with the angle bracket characters.)<br> Let me explain further - The column I am pulling out of the db is a text type column. Inside the text field, I have strange binary characters and get errors like this: 10668,17: FATAL ERROR: Invalid character (Unicode: 0xe) What can I do to rid myself of these characters via Stylus Studio? What I'm doing with the data once I've gotten it from SQL/XML is transforming it with XSLT (with XSLT mapper), and I cannot with a non well-formed document as a source document.

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 22 Feb 2006 01:05 PM

Ok - I ended up running the generated XML through a perl script to strip out those characters. Is there any way this can be done on import of the data via SQL/XML?

(Deleted User)

Subject: Invalid characters on relational data import
Author: (Deleted User)
Date: 22 Feb 2006 02:09 PM

Hi, Justin. Can you help us understand better what's going on by answering a few questions:

1. What database are you accessing?
2. What's the datatype of the text fields that contain the types of characters that are giving you problems.

Also, if you could provide a sample of the SQL/XML and resulting XML, that would be great. Please send what you can to stylus-field-report at progress dot com.

Thanks.

David Foster
Stylus Studio Team

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 22 Feb 2006 05:29 PM

David,

Thanks for the reply.

1. RDBMS is Microsoft SQL Server 2000, SP4.

2. The datatype of the field is text. A lot of these offending characters come from details of attacks/viruses launched against our customers webservers.

The regexp I used to strip the resulting XML is:

s/[\x00-\x08\x0b-\x0c\x0e-\x1f]//g

The SQL/XML is:

SELECT
XMLELEMENT(name "row",
XMLELEMENT(name "Company",t.Company),
XMLELEMENT(name "TicketID",t.TicketID),
XMLELEMENT(name "ticketdate",t.ticketdate),
XMLELEMENT(name "tickettime",t.tickettime),
XMLELEMENT(name "owner",t.owner),
XMLELEMENT(name "Subject",t.Subject),
XMLELEMENT(name "description",t.description),
XMLELEMENT(name "referenceID",t.referenceID),
XMLELEMENT(name "status",t.status)
)
FROM AbacBill.dbo.InitialTicketsImport t

Thanks,
-j

>Hi, Justin. Can you help us
>understand better what's going
>on by answering a few
>questions:
>
>1. What database are you
>accessing?
>2. What's the datatype of the
>text fields that contain the
>types of characters that are
>giving you problems.
>
>Also, if you could provide a
>sample of the SQL/XML and
>resulting XML, that would be
>great. Please send what you
>can to stylus-field-report at
>progress dot com.
>
>Thanks.
>
>David Foster
>Stylus Studio Team

(Deleted User)

Subject: Invalid characters on relational data import
Author: (Deleted User)
Date: 22 Feb 2006 08:32 PM

Hi, Justin. One more (picky) question: what type of text field? char? varchar? Knowing this might help.

Thanks again.

David Foster
Stylus Studio Team

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 22 Feb 2006 09:00 PM

David,

The def of the description column is:

Description(text, null)

When I look at the properties of the field in Enterprise Manager, it says "Data Type : Text".

Ivan Pedruzzi

Subject: Invalid characters on relational data import
Author: Ivan Pedruzzi
Date: 23 Feb 2006 06:51 PM

Hi Justin,

What I found is a bit ugly but should avoid to post process the result, see the attachment.

Justin Ream

Subject: Invalid characters on relational data import
Author: Justin Ream
Date: 23 Feb 2006 07:11 PM

Holy crap that's crazy.

I do get data type text is invalid for argument 1 of replace function returned from SQL Server. If I cast out the field, my data gets truncated ...

Ivan Pedruzzi

Subject: Invalid characters on relational data import
Author: Ivan Pedruzzi
Date: 23 Feb 2006 10:41 PM

Justin,

Try to replace t.description with SUBSTRING( t.description, 1, 1024)

Hope this helps
Ivan Pedruzzi
Stylus Studio Team

Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.