XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
search
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Home » Boards » Stylus Studio Developer Network » Stylus Studio Technical Forum » Invalid characters on relational data import
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> + Unresponsive - Custom XML Conv... (2)
-> - Need Help defining Flat File w... (1)
-> + XML to Database (6)
-> + XSD path when associating to a... (3)
-> - Auto EDI File Translation (1)
-> + Need help parsing EDI and crea... (2)
-> + Bug in generating Xml from Dtd (7)
-> + Stylus Studio 15, install erro... (4)
-> + How to create UML from XSD (2)
-> + Java configuration (4)
-> + embedding/starting X15 dircet ... (2)
-> + Stylus X15 R2 environment crip... (5)
-> + Output Method TEXT does not wo... (6)
-> + CONV loses its format on re-op... (5)
-> - Trouble Converting 846 EDI Fil... (1)
-> + XML Catalog support - DITA (2)
-> + non-persistent VDI, App-V Lice... (2)
-> + Can we READ XLS file and Conve... (8)
-> + Mapping XML to XML (3)
-> + SS X15 - Getting Error with 45... (2)
-> + Optimal for DB to XML? (2)
-> + Issues exporting SQL Server ->... (2)
-> + need additinal field in XML (2)
-> + blinking (3)
-> + HELP ANYONE PLEASE (3)
-> + xi:include including include s... (2)
-> + Validating an XML file against... (2)
-> + XML to EDI Telco D.0 field len... (2)
-> + Converting EDIFACT to XML in c... (3)
-> + XSLT Transformation fails with... (4)
-> + StylusStudio X15 R2 java Auto ... (6)
-> + StylusStudio X15 Review - firs... (2)
-> + Stylus Studio X15 stuck on pro... (11)
-> + Error handling in Coverter fil... (2)
-> + RESTful Web Services in Stylus... (3)
-> + How to produce a human-readabl... (3)
-> + EDI 945 to AX (2)
-> + Help x15 failing install (5)
-> + XSD 1.0/1.1 schema validation ... (3)
-> + Java Runtime Environment 8.0 (3)
-> + Web Service Call to Oracle OSM... (2)
-> + Telco EDI to XML output on opt... (12)
-> + Authentication failed (2)
-> + no declaration found for eleme... (2)
-> + Component installation failed (5)
-> - Component installation failed (1)
-> - AUP to expire (1)
-> + Disable Automatic Updates in S... (2)
-> + Windows 7 64 bit compatibility (2)
-> + EDI 8.1 to 10.6 (2)
-> - Conerting EDI 8.1 to EDI 10.6 ... (1)
-> + Debugging with Java built-in p... (3)
-> + Moving Stylus Studio (2)
-> + When using an xsl:import file,... (3)
-> + Generate XSD file from XML fil... (2)
-> + Custom XML Conversion (2)
-> + Create XML from XSD schema fil... (4)
-> + Slimed by Microsoft Word 2010 (6)
-> + XML to EDI (Telco D.0) convers... (3)
-> + XML Pipeline Troubleshooting (10)
-> + Allow negative values in Telco... (7)
-> + Unable to add new arguments to... (2)
-> + Validating XML Document (4)
-> + Debugging with X15 and MSXML p... (2)
-> + Parameter values (5)
-> + Limit on size of schema/instan... (4)
-> + Enterprise 12r1-The decompress... (5)
-> + Lost my copy - how do I renew? (2)
-> + Add Arguments to JVM does not ... (10)
-> + Page-position='last' not inple... (3)
-> + Decode the base 64 encrypted f... (2)
-> + XALAN Processor (4)
-> + How to create an XSD for flat ... (4)
-> + xslt saxon processor failure (3)
-> + Variables window Context path ... (4)
-> + XLS Formatting Objects Preview... (7)
-> + German DTA File (2)
-> + EDI to XML and LX segment (2)
-> - The element with name "F0101Z2... (1)
-> + How to insert the current date... (3)
-> + XSD - XML Sample (4)
-> + XSD to XML (3)
-> + Java extension functions, Xala... (2)
-> + XML with XSD to PDF (7)
-> + mapped network drives not show... (5)
-> + WCF Message size (2)
-> + Re-imaged desktop/laptop lost ... (4)
-> + Relative path in Stylus Studio (2)
-> + & and & in web service cal... (8)
-> + Which JRE do I need now? (3)
-> + Line Delimitted File Conversio... (4)
-> + Generating EDI Data from XML w... (2)
-> + Generating an XML file using C... (2)
-> - Generating an XML file from (1)
-> + output document cannot be pars... (10)
-> + convert RELAX NG to xsd (2)
-> + Intended Use of a .sef File (3)
-> + Skipping invalid records on SQ... (5)
-> + EDI XML Converters Inserting "... (2)
-> + Trying to use EDI to XSD docum... (5)
-- Previous [121-140] [141-160] [161-180] 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  
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
   
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.