Forums » Help »
saxon-sql problem
Added by Anonymous over 15 years ago
Legacy ID: #5724389
Legacy Poster: Shalom Carmel (scarmel)
I have been playing with the SQL extension of Saxon. I am trying to insert into a
database a record based on a simple XML file. This is the basic code
<xsl:for-each select="ROW"> <sql:insert
connection="$connection" table="KFXT">
<sql:column name="TCCMPN"
select="normalize-space(WO)"/> <sql:column
name="TCPROD"
select="normalize-space(ITEM)"/> <sql:column
name="TCFAC"
select="normalize-space(FACILITY)"/> <sql:column
name="TCRDTE"
select="normalize-space(START_DATE)"/> <sql:column
name="TCDDTE"
select="normalize-space(DUE_DATE)"/> <sql:column
name="TCQORD"
select="normalize-space(QUANTITY)"/> <sql:column
name="TCTRID"
select="normalize-space(TV_TRANSACTION_ID)"/>
<sql:column name="TCDATE"
select="normalize-space(INTERFACE_TRANSFER_DATE)"/>
<sql:column name="TCTIME"
select="normalize-space(INTERFACE_TRANSFER_TIME)"/>
</sql:insert> </xsl:for-each> Whenever all elements exist,
everything works well. However, the INTERFACE_TRANSFER_DATE and INTERFACE_TRANSFER_TIME
elements are optional and may not exist in a real world scenario. Whenever these element
do not exist, I must either not include them in the INSERT statement, or provide default
constants. I tried to condition these elements by this (non working) code, by using an
if statement: <xsl:for-each select="ROW">
<sql:insert connection="$connection"
table="KFXT"> <sql:column
name="TCCMPN" select="normalize-space(WO)"/>
<sql:column name="TCPROD"
select="normalize-space(ITEM)"/> <sql:column
name="TCFAC"
select="normalize-space(FACILITY)"/> <sql:column
name="TCRDTE"
select="normalize-space(START_DATE)"/> <sql:column
name="TCDDTE"
select="normalize-space(DUE_DATE)"/> <sql:column
name="TCQORD"
select="normalize-space(QUANTITY)"/> <sql:column
name="TCTRID"
select="normalize-space(TV_TRANSACTION_ID)"/> <xsl:if
test="string-length(normalize-space(INTERFACE_TRANSFER_DATE)) !=
0"> <sql:column name="TCDATE"
select="normalize-space(INTERFACE_TRANSFER_DATE)"/>
</xsl:if> <xsl:if
test="string-length(normalize-space(INTERFACE_TRANSFER_TIME)) !=
0"> <sql:column name="TCTIME"
select="normalize-space(INTERFACE_TRANSFER_TIME)"/>
</xsl:if> </sql:insert> </xsl:for-each> I get
the following error XTSE0010: An xsl:if element must not contain an sql:column element
So, how can I either omit the inclusion of non-existing elements in an INSERT statement,
or alternatively supply default values for the missing elements?
Legacy ID: #5724414
Legacy Poster: Michael Kay (mhkay)
sql:column isn't really an instruction, so it cant' go within xsl:if. You can either
do xsl:when test="A" sql:insert sql:column sql:column sql:column
/sql:insert /xsl:when xsl:when test="B" sql:insert sql:column
sql:column /sql:insert /xsl:when or you can put conditional logic inside the select
sql:column select="if(xxxx) then yyyy else zzzz" But if the column
doesn't exist in the database table then you'll have to use the first approach.
Legacy ID: #5738677
Legacy Poster: Shalom Carmel (scarmel)
thanks. the second approach worked.
Please register to reply
.