Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:14 Aug 2008 11:30 AM
OK, so far so good. I have a fairly complex relational db => XML transform involving joins on about 40 tables. Good news is that it works, and very quickly (when implemented using a FOR XML style query on SQL Server it worked, but slowly, a 10 record pull would take 10 mins - now, using XQUERY I can get 1000 records in about 30 secs). Bad news is that I need help with the next bit. I can chunk out small files manually (run the XQUERY and save off the preview results), but the is a fairly manual process and I seem to be limited to 1-5000 record pulls before bad things happen (JAVA heap/out of memory errors). I currently have about 470,000 records that I need to process (with substantially more to follow). How can I automate this? I would like to launch something that would build a series of xml files (each containing, say, 10000 records... maybe 10MB) that would constitute a complete dump. How do I begin?
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:14 Aug 2008 11:42 AM
This is the typical mechanism we use to split large tables into multiple XML files; you can probably adapt it to work in your specific context:
declare variable $pageSize := 1000;
let $table := collection("myTable")/myTable
let $groupCount := xs:integer(fn:ceiling(count($table) div $pageSize))
for $g in 1 to $groupCount
let $group := $table[fn:position() gt ($g - 1) * $pageSize
and fn:position() le $g * $pageSize]
return (ddtek:serialize-to-url(
<root>{$group}</root>,
concat("file:///c:/temp/split",$g,".xml"), "indent=yes"))
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:14 Aug 2008 04:22 PM
Unfortunately v2007 bundles DataDirect XQuery 3.0, which doesn't support serialize-to-url().
If you have active AUP (Annual Upgrade Protection), you can upgrade for free to v2008 Release 2, which bundles DataDirect XQuery 3.1 (www.stylusstudio.com/aup). If you want to try it out, you can download and install it from http://www.stylusstudio.com/download; you don't need to uninstall v2007.
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:19 Aug 2008 03:26 PM
**********************************************************************
**********************************************************************
**********************************************************************
ok, finally got 2008 activated... now, I have a question about the code you posted:
**********************************************************************
**********************************************************************
**********************************************************************
declare variable $pageSize := 1000;
let $table := collection("xAce.dbo.singles")/singles
let $groupCount := xs:integer(fn:ceiling(count($table) div $pageSize))
for $g in 1 to $groupCount
let $group := $table[fn:position() gt ($g - 1) * $pageSize and fn:position() le $g * $pageSize]
return (ddtek:serialize-to-url(
<Papers>
{
for $singles1 in collection("xAce.dbo.singles")/singles
where ($singles1/autoID >= -10) and ($singles1/autoID <= -1)
return
**********************************************************************
**********************************************************************
**********************************************************************
my question is here, shouldn't this block hold some sort of reference to the group collection rather than the original where clause I was using???
**********************************************************************
**********************************************************************
**********************************************************************
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:19 Aug 2008 03:38 PM
...actually, you may want to move your where condition up...
declare variable $pageSize := 1000;
let $table := collection("xAce.dbo.singles")/singles[autoID >= -10 and autoID <= -1]
let $groupCount := xs:integer(fn:ceiling(count($table) div $pageSize))
for $g in 1 to $groupCount
let $group := $table[fn:position() gt ($g - 1) * $pageSize and fn:position() le $g * $pageSize]
return (ddtek:serialize-to-url(
<Papers>
{
for $singles1 in $group
return
<AcademicPaper>
...
com.ddtek.xquery3.XQException: [DataDirect][XQuery]Error during SQL setup: [SQLServer JDBC Driver]IO Error writing temp file: There is not enough space on the disk
at com.ddtek.xquery3.xqj.Util.createXQException(Util.java:241)
at com.ddtek.xquery3.xqj.layer.DDXQResultSequence.getXQueryStreamReader(DDXQResultSequence.java:588)
at com.ddtek.xquery3.xqj.layer.DDXQAbstractSequence.writeSequence(DDXQAbstractSequence.java:456)
Caused by: com.ddtek.xquery.util.XQueryException: [EX5100][DataDirect][XQuery]Error during SQL setup: [SQLServer JDBC Driver]IO Error writing temp file: There is not enough space on the disk
at com.ddtek.xquery.extractor.util.SqlUtil.createXQueryException(SqlUtil.java:202)
at com.ddtek.xquery.extractor.sql.generic.SqlGlobalCollection.RunSqlBatch(SqlGlobalCollection.java:1102)
at com.ddtek.xquery.extractor.sql.generic.SqlGlobalCollection.RunSqlBatch(SqlGlobalCollection.java:1123)
at com.ddtek.xquery.extractor.sql.generic.FactoryImpl.checkTempTable(FactoryImpl.java:173)
at com.ddtek.xquery.extractor.sql.generic.SqlGlobalCollection.init(SqlGlobalCollection.java:112)
at com.ddtek.xquery.extractor.ExtractorStatement.createSQL(ExtractorStatement.java:508)
at com.ddtek.xquery.extractor.ExtractorStatement.prepareXQuery(ExtractorStatement.java:487)
at com.ddtek.xquery.extractor.ExtractorPreparedStatement.<init>(ExtractorPreparedStatement.java:48)
at com.ddtek.xquery.extractor.ExtractorConnection.prepareStatement(ExtractorConnection.java:244)
at com.ddtek.xquery.extractor.ExtractorConnection.prepareStatement(ExtractorConnection.java:232)
at com.ddtek.xquery.mediator.plan.SourcePhysOpImpl._prepareStatement(SourcePhysOpImpl.java:480)
at com.ddtek.xquery.mediator.plan.SourcePhysOpImpl._prepareStatement(SourcePhysOpImpl.java:523)
at com.ddtek.xquery.mediator.plan.SourcePhysOpImpl$SourceContext.setQuery(SourcePhysOpImpl.java:1505)
at com.ddtek.xquery.mediator.plan.BatchSourcePhysOpImpl.execute(BatchSourcePhysOpImpl.java:173)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:106)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:103)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.fillBatch(BatchJoinPhysOpImpl.java:140)
at com.ddtek.xquery.mediator.plan.BatchJoinPhysOpImpl.generateNextXTuple(BatchJoinPhysOpImpl.java:89)
at com.ddtek.xquery.mediator.plan.JoinPhysOpImpl.next(JoinPhysOpImpl.java:92)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.JoinLetTagPhysOpImpl.next(JoinLetTagPhysOpImpl.java:120)
at com.ddtek.xquery.mediator.xtuple.LetReturnSequence._materialize(LetReturnSequence.java:118)
at com.ddtek.xquery.mediator.xtuple.Sequence.materialize(Sequence.java:125)
at com.ddtek.xquery.mediator.xtuple.Sequence.getValue(Sequence.java:148)
at com.ddtek.xquery.mediator.plan.ExecutionPlanContextImpl.copyCurrentVariableValues(ExecutionPlanContextImpl.java:67)
at com.ddtek.xquery.mediator.xtuple.ExpressionEvaluator.evaluateExpression(ExpressionEvaluator.java:583)
at com.ddtek.xquery.mediator.xtuple.ExpressionEvaluator.evaluateExpressionToValue(ExpressionEvaluator.java:491)
at com.ddtek.xquery.mediator.xtuple.TagSequence._materialize(TagSequence.java:66)
at com.ddtek.xquery.mediator.xtuple.Sequence.materialize(Sequence.java:125)
at com.ddtek.xquery.mediator.xtuple.Sequence.getValue(Sequence.java:148)
at com.ddtek.xquery.mediator.plan.ExecutionPlanContextImpl.copyCurrentVariableValues(ExecutionPlanContextImpl.java:67)
at com.ddtek.xquery.mediator.xtuple.ExpressionEvaluator.evaluateExpression(ExpressionEvaluator.java:583)
at com.ddtek.xquery.mediator.xtuple.ExpressionEvaluator.evaluateExpressionToSequenceIterator(ExpressionEvaluator.java:507)
at com.ddtek.xquery.mediator.plan.SaxonSourcePhysOpImpl.execute(SaxonSourcePhysOpImpl.java:185)
at com.ddtek.xquery.mediator.plan.JoinSaxonSourcePhysOpImpl.next(JoinSaxonSourcePhysOpImpl.java:108)
at com.ddtek.xquery.mediator.plan.ProjectPhysOpImpl.next(ProjectPhysOpImpl.java:74)
at com.ddtek.xquery.mediator.plan.ReturnPhysOpImpl.getNextSequenceUngrouper(ReturnPhysOpImpl.java:105)
at com.ddtek.xquery.mediator.plan.ReturnPhysOpImpl.next(ReturnPhysOpImpl.java:62)
at com.ddtek.xquery.mediator.plan.ExecutionPlanImpl.next(ExecutionPlanImpl.java:301)
at com.ddtek.xquery.mediator.plan.DefaultPlanIterator.getAsXQueryStreamReader(DefaultPlanIterator.java:109)
at com.ddtek.xquery3.xqj.layer.DDXQResultSequence.getXQueryStreamReader(DDXQResultSequence.java:577)
... 1 more
Caused by: com.ddtek.xquery.jdbc.base.BaseBatchUpdateException: [XQuery][SQLServer JDBC Driver]IO Error writing temp file: There is not enough space on the disk
at com.ddtek.xquery.jdbc.base.BaseStatement.executeOneStatementAtATime(Unknown Source)
at com.ddtek.xquery.jdbc.base.BaseStatement.executeBatchEmulation(Unknown Source)
at com.ddtek.xquery.jdbc.base.BaseStatement.executeBatch(Unknown Source)
at com.ddtek.xquery.extractor.sql.generic.SqlGlobalCollection.RunSqlBatch(SqlGlobalCollection.java:1098)
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:19 Aug 2008 04:07 PM
Hm, interesting; that's an error triggered by the underlying JDBC driver. The drivers attempt to create temporary files using the location specified by the Java system property "java.io.tmpdir".
So, if you are running it from inside Stylus Studio, you'll need to bring up the Tools > Options > Java Virtual Machine page, and add something like...
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:19 Aug 2008 04:30 PM
In the registry:
HKEY_CURRENT_USER\Software\Stylus Studio\2008 XML Enterprise Suite Release 2\Runtime Settings
The affected value should be "JVM Args".
Can you copy and paste that value here before you remove the offending entry? A similar option doesn't seem to negatively affect my Stylus Studio copy.
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:19 Aug 2008 04:43 PM
operator error operator error...
being severely Java challenged, I misunderstood the delimiters and put [,-D] at the end instead of [ -D]... regedit fixed...
now I'm getting java heap errors... which previously has meant I was trying to pull too many records at once. So I took it down from 1000 to 100 on the group size, but still getting the same problem.
Back when I ran it with the -10 to -1 where clause in place, it ran fine (except that each file was the same 10 records). Attached is my current xquery file...
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:19 Aug 2008 04:52 PM
The error seems to be still happening at the JDBC level; I'm not sure that will help, but can you try boosting your JVM heap settings a bit? In the same field where you have the temporary folder setting, also add: -Xmx512m
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:19 Aug 2008 06:06 PM
aye, thank-you for the help.
Looks like I may have found something that heads towards a solution. What I have done is to reduce the size of the driving table (singles) by pulling the Abstracts into a separate table. I also reduced the size of the group (it's at 100 right now, and I should be able to bump that back up now). I think the main offender (hitting the heap) was the varchar(max) field for the abstracts. Going to let this run for a few hours, then modify the parameters a bit and try again (would prefer not to have 4,770 files if I can help it).
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:20 Aug 2008 08:26 AM
If you have a column with very large content, that would also explain the I/O out of space error you initially got. And would likely explain why you are running out of heap space; in the current release, serialize-to-url will instantiate in memory the fragment that it needs to serialize (one by one); if each individual segment is large, you'll end up hitting the problem you are describing.
The workaround would be to not use serialize-to-url and return a sequence of XML fragments as result; and then to stream them to file(s) through the XQJ API. As long as you have a valid DataDirect XQuery license (Stylus Studio grants you evaluation rights to such API for 14 days after installing the application), you could try using the attached query and ask Stylus Studio to generate the Java code for you (XQuery menu). Change the XQuery execution line to save the result to a file, from:
xqExpr.executeQuery().writeSequenceToResult(new StreamResult(outWriter));
...to...
xqExpr.executeQuery().writeSequenceToResult(new StreamResult(new File("c:/huge.xml")));
The generated sample app will create a single very large file (not well formed; it will have multiple document elements), but that would be a start to see if that would solve heap space issues.
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:20 Aug 2008 01:42 PM
I'll give that a try once I get the overall transformation nailed down. Speaking of which, I'm still having some difficulty with empty nodes. Keeping the empty elements from appearing is working great, where I'm having problems is with sequences. See attached. When I structure it like:
for $TA in collection("xAce.dbo.TA")/TA
where $singles1/autoID = $TA/autoID
return
<TaxonomicData>
<Taxonomic>
{
$TA/TA/text()
}
</Taxonomic>
</TaxonomicData>
}
it's not present when empty, but when it has data, the xml is not structured like I want. I get:
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:20 Aug 2008 01:53 PM
<TaxonomicData> {
for $TA in collection("xAce.dbo.TA")/TA
where $singles1/autoID = $TA/autoID
return
if ($TA/TA) then
<Taxonomic>
{
$TA/TA/text()
}
</Taxonomic>
else ()
}
</TaxonomicData>
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:20 Aug 2008 02:37 PM
hmmm, still not working, what is going on?
I put in:
<TaxonomicData>
{
for $TA in collection("xAce.dbo.TA")/TA
where $singles1/autoID = $TA/autoID
return
if ($TA/TA) then
<Taxonomic>
{
$TA/TA/text()
}
</Taxonomic>
else ()
}
</TaxonomicData>
which I think matches what you put,
but still I'm getting:
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:20 Aug 2008 02:51 PM
...that's because you have no rows in the table matching the where condition; so, maybe what you are really trying to do is (I didn't check the grammar very carefully):
let $TAs := collection("xAce.dbo.TA")/TA[$singles1/autoID = autoID]
return
if ($TAs) then
<TaxonomicData>
{
for $TA in $TAs
return
<Taxonomic>{$TA/TA/text()}</Taxonomic>
}
</TaxonomicData>
else ()
...if you can also have empty TA *columns* then things are a bit more complicated:
(: first create a sequence containing all non-null TA column strings :)
let $TAs := for $TA in collection("xAce.dbo.TA")/TA[$singles1/autoID = autoID] return if($TA/TA) then $TA/TA/text() else ()
return
(: if there is at least one non-null TA column value, then create the structure :)
if ($TAs) then
<TaxonomicData>
{
for $TA in $TAs
return
<Taxonomic>{$TA}</Taxonomic>
}
</TaxonomicData>
else ()
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:20 Aug 2008 03:05 PM
ahhh, ok, that is indeed what I needed. The first snippet fixes the problem I was having with the taxonomic data (now I just need to replicate it in the other dozen or so places), and the second snippet looks like it may be good for the other case I have, which is more complicated... I have a sequence that looks like:
the fun part is that each sequence will have either element1 or element2, and if element2, then maybe element3, and of course, each record may or may not have the sequence at all...
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:21 Aug 2008 11:46 PM
OK, got the complete transform done. Now the performance questions. In preview mode, I can pull 10,000 records at a time in ~65 secs. But when I implemented the serial to url automated method, I was reduced to generating 250 record files, each one taking about 10 mins. After letting it run for about 18 hours I had to shut it down and go back to the manual method. Automated was going to take about 10 days (I had maybe 2 available), whereas, while painful, I was able to process all 475,000 records in a bit under 3 hours. Is there a way to improve the automated method? Would the 'really large file' approach you mentioned be speedy? Or would it be possible to set up some kind of external script to can the ranges and pass in parameters?
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:22 Aug 2008 08:23 AM
Both alternatives you are mentioning would likely be much more performant; as I mentioned, serialize-to-url creates an in-memory representation of the document that needs to be created, which likely slows down things dramatically in your scenario.
You can try the creation of the large file through the API - and that should give you a good feeling of what's the real time it would take to process and serialize the whole data set. And you can also use external variables in XQuery and bind them dynamically through Java to run multiple XQueries that take care of your problem.
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:22 Aug 2008 08:49 AM
The API is a Java API; assuming you have a standard Stylus Studio Enterprise installation, you can find the following documentation:
- JavaDoc: c:\Program Files\Stylus Studio 2008 XML Enterprise Suite Release 2\Components\DataDirect XQuery\javadoc
- User guide: c:\Program Files\Stylus Studio 2008 XML Enterprise Suite Release 2\Components\DataDirect XQuery\books\ddxquery\books.pdf
- Examples: c:\Program Files\Stylus Studio 2008 XML Enterprise Suite Release 2\Components\DataDirect XQuery\examples
Subject:Complete SQL Server database transform to XML Author:Bernard Quinn Date:02 Sep 2008 09:26 PM
haven't had a chance to play with the API yet... however, I have what should be a simple question. For my xquery, I started with a fairly detailed xsd, but over the course of multiple revisions, my target document no longer matches the original. Some nodes no longer mapped, and some new nodes. How do I push these changes back to the xsd file? (or save a new xsd which reflects the current mappings).
Subject:Complete SQL Server database transform to XML Author:Minollo I. Date:02 Sep 2008 10:32 PM
You mean you didn't change the underlying target schema, but you changed the target tree in mapper? And you are looking to push those mapper changes into the original XML Schema? That operation is not supported; the target panel in Mapper is meant to display a schema structure and it allows you to augment it, but it's not able to edit the XML Schema itself...