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
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext 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

 
Topic Page 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Go to previous topicPrev TopicGo to next topicNext Topic
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.