SSDN - Invalid characters on relational data import

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Conferences

	Stylus Studio Feature Requests (1192)
	Stylus Studio Technical Forum (14621)
	Stylus Studio - Registrar en o... (1)
	Stylus Studio - Registrar en o... (2)
	Can a pipeline send a file by ... (2)
	After Updateing WIN10 to WIN11... (12)
	Where do I add the custom java... (3)
	Where is the Diagram tab? (5)
	Applying XSLT to Word DOCX/XML (2)
	CSV conversion via ConvertToXM... (1)
	Text symbols in SS not same as... (4)
	Exposing xquery as webservice ... (6)
	Syntax Identifier (2)
	Saving a Converted XML as an X... (5)
	Output document cannot be pars... (4)
	Archiving output from conversi... (1)
	EDIFACT guideline from Stylus ... (3)
	CSV file putting all the data ... (5)
	Can't install Home version 64b... (5)
	presale - Can I covers this sc... (5)
	Problem with UNB (5)
	Splitting EDIFACT files pipeli... (4)
	[1-20] [21-40] [41-60] Next
	Website Feedback (249)
	XSLT Help and Discussion (7625)
	XQuery Help and Discussion (2017)
	Stylus Studio FAQs (159)
	Stylus Studio Code Samples & Utilities (364)
	Stylus Studio Announcements (113)

Topic

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

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.