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
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
- Stylus Studio Technical Forum (14621)
-> - 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)
-> + Specifying EDI Delimiters (2)
-> + Defining 'connection string' f... (9)
-> + Help Programatically Convertin... (2)
-> + Using adapter URL in pipeline. (7)
-> + How to enable EDI functionalit... (2)
-> + Unable to open file with '%2F'... (2)
-> + How to create a dependency gra... (2)
-> + If block (2)
-> + Mapping multiple files within ... (7)
-> + StylusStudio vs Altova vs BizT... (4)
-> + Command line execution (7)
-> + CSS and Styling in X15 (2)
-> + Ignores debug breakpoints in X... (5)
-- Previous [181-200] [201-220] [221-240] 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.