XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Justin ReamSubject: 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?

Postnext
Justin ReamSubject: 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.

Postnext
Justin ReamSubject: 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?

Postnext
(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

Postnext
Justin ReamSubject: 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

Postnext
(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

Postnext
Justin ReamSubject: 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".

Postnext
Ivan PedruzziSubject: 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.


Documentsqlxml.txt

Postnext
Justin ReamSubject: 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 ...

Posttop
Ivan PedruzziSubject: 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 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! 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

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
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.