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 » Skipping invalid records on SQL insert
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)
-> ->Skipping invalid records ...
-> ->Skipping invalid records ...
-> ->Skipping invalid records ...
-> ->Skipping invalid records ...
-> + 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
chris miszturSubject: Skipping invalid records on SQL insert
Author: chris misztur
Date: 19 Jun 2013 04:34 PM
Is there a way to skip over duplicate inserts w/o aborting xquery execution?

javax.xml.xquery.XQException: [DataDirect][XQuery]Error during SQL execution: [SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'F46010_PK'. Cannot insert duplicate key in object 'TESTDTA.F46010'. The duplicate key value is ( 1200, 740682).
Postnext
Ivan PedruzziSubject: Skipping invalid records on SQL insert
Author: Ivan Pedruzzi
Date: 20 Jun 2013 05:26 AM

You need to filter out the duplicates in the logic which iterates through the input data, look at function distinct-values.
http://www.w3.org/TR/xpath-functions/#func-distinct-values

If you need additional help, post your query with a sample of the XML input

Ivan Pedruzzi
Stylus Studio Team

Postnext
chris miszturSubject: Skipping invalid records on SQL insert
Author: chris misztur
Date: 20 Jun 2013 07:41 AM
sorry, actually the issue is that some of the records being inserted already exist in the database.

Postnext
chris miszturSubject: Skipping invalid records on SQL insert
Author: chris misztur
Date: 20 Jun 2013 08:39 AM
Is this good practice?

for $row in /a:f46010/a:row
return
if(exists(collection("JDE_DEVELOPMENT.TESTDTA.F46010")/F46010[IPMCU/text() = mf:pad-string-to-length-left(' ',$row/a:MCU,12)and IPITM/text() = $row/a:ITM]))
then ()
else (
ddtek:sql-insert("sqlserver_us09sqldev_1433:JDE_DEVELOPMENT.TESTDTA.F46010",
"IPMCU",mf:pad-string-to-length-left(' ',$row/a:MCU,12),
"IPITM",$row/a:ITM,
"IPMLIT",$row/a:MLIT,
"IPMLOT",$row/a:MLOT,
"IPSPLP",$row/a:SPLP,
"IPPTPH",$row/a:PTPH,
"IPPKPH",$row/a:PKPH,
"IPRPPH",$row/a:RPPH,
"IPDLTN",$row/a:DLTN,
"IPVLOC",$row/a:VLOC,
"IPHLOC",$row/a:HLOC,
"IPPPUT",$row/a:PPUT,
"IPPPIK",$row/a:PPIK,
"IPFIFR",$row/a:FIFR,
"IPUSER",mf:getAuditInformation()/@user)
)
Posttop
Ivan PedruzziSubject: Skipping invalid records on SQL insert
Author: Ivan Pedruzzi
Date: 20 Jun 2013 12:11 PM

Look at the query plan on this version see if you gain

for $row in /a:f46010/a:row
,$pad in mf:pad-string-to-length-left(' ',$row/a:MCU,12)
where not(exists(collection("JDE_DEVELOPMENT.TESTDTA.F46010")/F46010[IPMCU/text() = $pad and IPITM/text() = $row/a:ITM]))
return
ddtek:sql-insert("sqlserver_us09sqldev_1433:JDE_DEVELOPMENT.TESTDTA.F46010",
"IPMCU",mf:pad-string-to-length-left(' ',$row/a:MCU,12),
"IPITM",$row/a:ITM,
"IPMLIT",$row/a:MLIT,
"IPMLOT",$row/a:MLOT,
"IPSPLP",$row/a:SPLP,
"IPPTPH",$row/a:PTPH,
"IPPKPH",$row/a:PKPH,
"IPRPPH",$row/a:RPPH,
"IPDLTN",$row/a:DLTN,
"IPVLOC",$row/a:VLOC,
"IPHLOC",$row/a:HLOC,
"IPPPUT",$row/a:PPUT,
"IPPPIK",$row/a:PPIK,
"IPFIFR",$row/a:FIFR,
"IPUSER",mf:getAuditInformation()/@user)
)



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.