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)
-> + Grouping (2)
-> + Best Practice for Pipeline err... (2)
-> + pdf generation (4)
-> + Problems excuting a Pipeline w... (2)
-> + XML serializer (3)
-> + Supported EDIFACT versions (5)
-> - Anyone know XActimate ESX file... (1)
-> + Stylus Studio with OpenJDK (2)
-> - Encoding error when importing ... (1)
-> - SEO Service in Delhi, India (1)
-> - HP Printer Support (1)
-> + Stylus Studio X16 Professional... (5)
-> + Stylus Studio X16 Enterprise -... (4)
-> - Overly complicated formatting ... (1)
-> + java apparently running, canno... (2)
-> + Stylus on Citrix - unable to v... (6)
-> + XSLT and XPath Questions (New ... (13)
-> + EDIFACT D96A CONTRL XML schema (2)
-> + JDK dropdown box empty and Jav... (2)
-> + Replacing XSD files in Report (4)
-> + Cannot locate WYSIWIG tab on X... (3)
-> + EDI X12 xml schema with segmen... (2)
-> + Error Message (4)
-> + jvm disabled message keeps pop... (2)
-> + XTSE0620: An xsl:param element... (3)
-> + Fail in installing “Apache FOP... (5)
-> - Jenkins: about the path of bui... (1)
-> + problem with Stylus X15 XML RE... (2)
-> + Cannot remove a row into a tab... (6)
-> + prevent the meta data from bei... (3)
-> + Unable to auto detect JRE in X... (2)
-> - Unable to auto detect JRE in X... (1)
-> - where to find information (1)
-> + X16 - XPath 3.0, XQuery 3.0, X... (2)
-> - Test (1)
-> + XSLT debugger shows [symbol u... (3)
-> - How do i use *.conv to process... (1)
-> - How do i use *.conv to process... (1)
-> + Not able to configure JVM in ... (5)
-> + Converting Cobol flat file to ... (2)
-> + Problem with uninstall - Windo... (2)
-> + Function current() is not reco... (6)
-> + API or web version (2)
-> + How to use Saxon for X16 on WI... (9)
-> + Error in SS X14 syntax highlig... (2)
-> + Option not available (2)
-> + XML Diff functionality and sor... (3)
-> + Convert Oagis XML file to TXT (4)
-> + DataDirect xqresultsequence is... (2)
-> - xfile Legacy file format suppo... (1)
-> + TPFDF (3)
-> + Help needed with SLSRPT - EDIF... (2)
-> - keeps crashing after I try and... (1)
-> + Can SS do this? (14)
-> - CargoImp support (1)
-> + No Preview Displayed - File is... (3)
-> - Stylus Studio X16 build 1974l ... (1)
-> + Help! Help!Help! Regading form... (2)
-> + FileNotFoundException generati... (6)
-> + FO External Graphic Producing ... (2)
-> + Use of Java methodes (4)
-> + get generated XQuery output on... (2)
-> - right construct for involving ... (1)
-> + SS JRE Version has "Stylus Stu... (3)
-> + Trying to validate an XSD: co... (3)
-> + Disable "Output" windows (4)
-> + Stylus Studio V16 X64 update d... (2)
-> + Could not load the Java Virtua... (2)
-> + .NET XslCompileTransform strip... (6)
-> + XML Report Preview and Save Ev... (8)
-> + X16 Enterprise 32bit crash on ... (5)
-> - MSXML6.0 SAX Parser, "Index re... (1)
-> + MSXML6.0 DOM Parser stops afte... (4)
-> + How to disable checking for up... (2)
-> + XML Publisher requires duplica... (4)
-> + Modifying Field Name in XML (4)
-> + Java Runtime Environmnet Libra... (2)
-> + Passing Parameters and Multipl... (4)
-> + Total Newbie Question... where... (3)
-> + XML Pipeline (2)
-> + Encoding problem in text previ... (5)
-> + New Installation on windows 20... (4)
-> - Exception while calling Java m... (1)
-> + Jars to be included for callin... (2)
-> + SXXP0003: Error reported by XM... (2)
-> + Validation error (5)
-> + Windows 10, latest Java, new t... (2)
-> + using help crashes stylus stud... (3)
-> + Web Service Call error (5)
-> + javax.xml.xquery.XQException: ... (2)
-> + Trang translator is not availa... (2)
-> + Replacement for flat2xml (2)
-> + CONV CSV Display Problem (3)
-> + StylusValidator.exe Command Li... (3)
-> + Cannot run Java built in valid... (2)
-> + Windows 10??? (3)
-> + Stylus Custom XML Conversion (... (8)
-> + X14 enterprise and saxon 9.4.0... (3)
-> + Open an XML document in the XM... (3)
-> + How to generate mapping report... (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  
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.