Topic
  • 8 replies
  • Latest Post - ‏2011-07-08T16:08:50Z by BingSun
BingSun
BingSun
7 Posts

Pinned topic xmltable: how to number a row based on some value in table

‏2011-07-06T16:49:52Z |
I need to store some trade termination information in a table.
Trade termination includes both partial and full termination info. Partial termination happens before full termination.

The rows for partial termination are numbered using FOR ORDINALITY.

I need to number the full termination rows starting from the last number of partial termination row.

For example, if there are four partial termination rows, numbered by 1,2,3,4, then the first full termination row would be numbered 5 for the first trade leg.

Here are some code that does not work right with the TERMINATION_NUM field:

CREATE FUNCTION getLegTerminData(input VARCHAR(64))
RETURNS TABLE (
TRADE_KEY VARCHAR(64),
LEG_NUM BIGINT,
TERMINATION_NUM BIGINT,
TERMINATION_DATE VARCHAR(32),
TERMINATION_PERCENT DOUBLE,
TERMINATION_PAYMENT_DATE VARCHAR(32),
TERMINATION_DIRTY_PRICE DOUBLE,
TERMINATION_ACCRUED_INT DOUBLE
)
LANGUAGE SQL
RETURN

SELECT
t.TRADE_KEY,
CAST(t.LEG_NUM AS BIGINT) + 1 AS LEG_NUM,
COALESCE(t.TERMINATION_NUM, 0),
t.TERMINATION_DATE,
COALESCE(t.TERMINATION_PERCENT * 100.0, 0),
t.TERMINATION_PAYMENT_DATE,
COALESCE(t.TERMINATION_DIRTY_PRICE, 0),
COALESCE(t.TERMINATION_ACCRUED_INT, 0)
FROM
WSD_TRADE_EXPORT trade,
XMLTABLE('$root/SPF_Trade/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination' passing trade.TRADE_XML as "root"
COLUMNS
TRADE_KEY VARCHAR(64) path '../../../../../../TradeKeys/FrontOfficeKey/Key/text()',
LEG_NUM BIGINT path '../../../LegNumber/text()',
TERMINATION_NUM FOR ORDINALITY,
TERMINATION_DATE VARCHAR(32) path 'TerminateDate/substring(text(), 1, 32)',
TERMINATION_PERCENT DOUBLE path 'PercentsTerminated/text()',
TERMINATION_PAYMENT_DATE VARCHAR(32) path 'TerminatePayDate/substring(text(), 1, 32)',
TERMINATION_DIRTY_PRICE DOUBLE path 'wsTermDirtyPrice/text()',
TERMINATION_ACCRUED_INT DOUBLE path 'TermInterestAccrued/text()') as t
WHERE
trade.TRADE_KEY IN (input)

UNION ALL

SELECT
t.TRADE_KEY,
CAST(t.LEG_NUM AS BIGINT) + 1 AS LEG_NUM,
CAST(t.TERMINATION_NUM AS BIGINT) + 1 AS TERMINATION_NUM,
t.TERMINATION_DATE,
COALESCE(t.TERMINATION_PERCENT, 0),
t.TERMINATION_PAYMENT_DATE,
COALESCE(t.TERMINATION_DIRTY_PRICE, 0),
COALESCE(t.TERMINATION_ACCRUED_INT, 0)
FROM
WSD_TRADE_EXPORT trade,
XMLTABLE('$root/SPF_Trade/TradeData/Legs/Leg' passing trade.TRADE_XML as "root"
COLUMNS
TRADE_KEY VARCHAR(64) path '../../TradeKeys/FrontOfficeKey/Key/text()',
LEG_NUM BIGINT path 'LegData/LegNumber/text()',
TERMINATION_NUM FOR ORDINALITY,
TERMINATION_DATE VARCHAR(32) path '../../TerminationDt/substring(text(), 1, 32)',
TERMINATION_PERCENT DOUBLE path 'let $i := 100.0 return $i',
TERMINATION_PAYMENT_DATE VARCHAR(32) path 'LegData/LegRecordData/TerminationPmtDt/substring(text(), 1, 32)',
TERMINATION_DIRTY_PRICE DOUBLE path 'LegData/LegRecordData/wsTermDirtyPrice/text()',
TERMINATION_ACCRUED_INT DOUBLE path 'LegData/LegRecordData/TermInterestAccrued/text()') as t
WHERE
trade.TRADE_KEY IN (input) AND TERMINATION_DATE != ''

GO

CREATE PROCEDURE spf_update_leg_termin_data(input VARCHAR(64))
LANGUAGE SQL
BEGIN

delete from SPF_ORD_LEG_TERMIN_DATA where TRADE_KEY in (input);
commit;
insert into SPF_ORD_LEG_TERMIN_DATA(
TRADE_KEY,
LEG_NUM,
TERMINATION_NUM,
TERMINATION_DATE,
TERMINATION_PERCENT,
TERMINATION_PAYMENT_DATE,
TERMINATION_DIRTY_PRICE,
TERMINATION_ACCRUED_INT
)
select * from TABLE(getLegTerminData(input)) as t
;
commit;
END
GO
Updated on 2011-07-08T16:08:50Z at 2011-07-08T16:08:50Z by BingSun
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-06T17:35:11Z  
    Hi Bing,

    sounds like a tricky problem! To help you figure out how to accomplish this numbering, I think we need to see at least one sample XML document from the WSD_TRADE_EXPORT table. Maybe you can attach one document as a text file to your next post. Please also show the desired output that you want to produce from that document.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • BingSun
    BingSun
    7 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-06T18:00:05Z  
    Hi Bing,

    sounds like a tricky problem! To help you figure out how to accomplish this numbering, I think we need to see at least one sample XML document from the WSD_TRADE_EXPORT table. Maybe you can attach one document as a text file to your next post. Please also show the desired output that you want to produce from that document.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    I have attached one trade xml files.

    Here is the desired output:

    TRADE_KEY LEG_NUM TERMINATION_NUM TERMINATION_DATE TERMINATION_PERCENT TERMINATION_PAYMENT_DATE TERMINATION_DIRTY_PRICE TERMINATION_ACCRUED_INT
    4000054 1 5 2011-06-17 100.0 2011-07-08 -1113247.389461135 -11805.555555556
    4000054 2 6 2011-06-17 100.0 2011-07-08 1253458.315887796 13259.911928999
    4000054 1 1 2011-06-17 50.0 2011-07-08 -2226494.778922271 -23611.111111111
    4000054 1 2 2011-06-17 25.0 2011-07-08 -1113247.389461135 -11805.555555556
    4000054 2 3 2011-06-17 50.0 2011-07-08 2506916.631775593 26519.823857998
    4000054 2 4 2011-06-17 25.0 2011-07-08 1253458.315887796 13259.911928999
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-06T19:14:16Z  
    • BingSun
    • ‏2011-07-06T18:00:05Z
    I have attached one trade xml files.

    Here is the desired output:

    TRADE_KEY LEG_NUM TERMINATION_NUM TERMINATION_DATE TERMINATION_PERCENT TERMINATION_PAYMENT_DATE TERMINATION_DIRTY_PRICE TERMINATION_ACCRUED_INT
    4000054 1 5 2011-06-17 100.0 2011-07-08 -1113247.389461135 -11805.555555556
    4000054 2 6 2011-06-17 100.0 2011-07-08 1253458.315887796 13259.911928999
    4000054 1 1 2011-06-17 50.0 2011-07-08 -2226494.778922271 -23611.111111111
    4000054 1 2 2011-06-17 25.0 2011-07-08 -1113247.389461135 -11805.555555556
    4000054 2 3 2011-06-17 50.0 2011-07-08 2506916.631775593 26519.823857998
    4000054 2 4 2011-06-17 25.0 2011-07-08 1253458.315887796 13259.911928999
    Thanks, Bing.

    First, the XPath in your previous queries assume that the root element of the XML documents is <SPF_Trade>, but the the sample that you provided
    has <ObjectRoot> as the root element. No big deal, but this is why the XPath in my query below shows ObjectRoot rather than SPF_trade.

    If I understand your requirements correctly, then the query below produces the desired numbering (also see attached text file).

    For simplicity, I have just used a table with a single XML column.

    The key idea of my query is to use an expression of the form (path1, path2) as the row-generating expression in the XMLTABLE function.
    This way, a single XMLTABLE function produces rows for both paths (terminations and legs) as a single row set.
    The FOR ORDINALITY clause now numbers them as your sample output has indicated.
    The column-generating expressions in the XMLTABLE use if-then-else to return the correct element depending on whether the row belongs to path1 (termination) or path2 (leg).

    
    SELECT t.TRADE_KEY, CAST(t.LEG_NUM AS BIGINT) + 1 AS LEG_NUM, COALESCE(t.TERMINATION_NUM, 0) AS TERM_NUM, t.TERMINATION_DATE, COALESCE(t.TERMINATION_PERCENT * 100.0, 0) as PERC, t.TERMINATION_PAYMENT_DATE, COALESCE(t.TERMINATION_DIRTY_PRICE, 0), COALESCE(t.TERMINATION_ACCRUED_INT, 0) AS  ACCR FROM WSD_TRADE_EXPORT trade, XMLTABLE(
    '  ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData/Legs/Leg ) 
    ' passing trade.TRADE_XML as "root" COLUMNS TRADE_KEY VARCHAR(16) path 
    'if (name(.) = "Termination") then ../../../../../../TradeKeys/FrontOfficeKey/Key 
    
    else ../../TradeKeys/FrontOfficeKey/Key
    ', LEG_NUM BIGINT        path 
    'if (name(.) = "Termination") then ../../../LegNumber 
    
    else LegData/LegNumber
    ', TERMINATION_NUM FOR ORDINALITY, TERMINATION_DATE VARCHAR(32) path 
    'if (name(.) = "Termination") then TerminateDate/substring(. , 1, 32) 
    
    else ../../TerminationDt/substring( . , 1, 32)
    ', TERMINATION_PERCENT DECIMAL(5,2) path 
    'if (name(.) = "Termination") then PercentsTerminated 
    
    else 1
    ', TERMINATION_PAYMENT_DATE VARCHAR(32) path 
    'if (name(.) = "Termination") then TerminatePayDate/substring(. , 1, 32) 
    
    else LegData/LegRecordData/TerminationPmtDt/substring(. , 1, 32)
    ', TERMINATION_DIRTY_PRICE DOUBLE path 
    'if (name(.) = "Termination") then wsTermDirtyPrice 
    
    else LegData/LegRecordData/wsTermDirtyPrice
    ', TERMINATION_ACCRUED_INT DOUBLE path 
    'if (name(.) = "Termination") then TermInterestAccrued 
    
    else LegData/LegRecordData/TermInterestAccrued
    ') as t WHERE t.TRADE_KEY IN (4000054);       TRADE_KEY        LEG_NUM              TERM_NUM             TERMINATION_DATE                 PERC             TERMINATION_PAYMENT_DATE         7                        ACCR ---------------- -------------------- -------------------- -------------------------------- ---------------- -------------------------------- ------------------------ ------------------------ 4000054                             1                    1 2011-06-17                                 50.000 2011-07-08                         -2.22649477892227E+006   -2.36111111111110E+004 4000054                             1                    2 2011-06-17                                 25.000 2011-07-08                         -1.11324738946114E+006   -1.18055555555560E+004 4000054                             2                    3 2011-06-17                                 50.000 2011-07-08                         +2.50691663177559E+006   +2.65198238579980E+004 4000054                             2                    4 2011-06-17 25.000 2011-07-08                         +1.25345831588780E+006   +1.32599119289990E+004 4000054                             1                    5 2011-06-17                                100.000 2011-07-08                         -1.11324738946114E+006   -1.18055555555560E+004 4000054                             2                    6 2011-06-17                                100.000 2011-07-08                         +1.25345831588780E+006   +1.32599119289990E+004   6 record(s) selected.
    


    Does this solve your problem? Let me know if not.

    There are probably other ways to achieve the same result, but this was the most intuitive I could think of right now.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • BingSun
    BingSun
    7 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-06T20:23:43Z  
    Thanks, Bing.

    First, the XPath in your previous queries assume that the root element of the XML documents is <SPF_Trade>, but the the sample that you provided
    has <ObjectRoot> as the root element. No big deal, but this is why the XPath in my query below shows ObjectRoot rather than SPF_trade.

    If I understand your requirements correctly, then the query below produces the desired numbering (also see attached text file).

    For simplicity, I have just used a table with a single XML column.

    The key idea of my query is to use an expression of the form (path1, path2) as the row-generating expression in the XMLTABLE function.
    This way, a single XMLTABLE function produces rows for both paths (terminations and legs) as a single row set.
    The FOR ORDINALITY clause now numbers them as your sample output has indicated.
    The column-generating expressions in the XMLTABLE use if-then-else to return the correct element depending on whether the row belongs to path1 (termination) or path2 (leg).

    <pre class="jive-pre"> SELECT t.TRADE_KEY, CAST(t.LEG_NUM AS BIGINT) + 1 AS LEG_NUM, COALESCE(t.TERMINATION_NUM, 0) AS TERM_NUM, t.TERMINATION_DATE, COALESCE(t.TERMINATION_PERCENT * 100.0, 0) as PERC, t.TERMINATION_PAYMENT_DATE, COALESCE(t.TERMINATION_DIRTY_PRICE, 0), COALESCE(t.TERMINATION_ACCRUED_INT, 0) AS ACCR FROM WSD_TRADE_EXPORT trade, XMLTABLE( ' ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData/Legs/Leg ) ' passing trade.TRADE_XML as "root" COLUMNS TRADE_KEY VARCHAR(16) path 'if (name(.) = "Termination") then ../../../../../../TradeKeys/FrontOfficeKey/Key else ../../TradeKeys/FrontOfficeKey/Key ', LEG_NUM BIGINT path 'if (name(.) = "Termination") then ../../../LegNumber else LegData/LegNumber ', TERMINATION_NUM FOR ORDINALITY, TERMINATION_DATE VARCHAR(32) path 'if (name(.) = "Termination") then TerminateDate/substring(. , 1, 32) else ../../TerminationDt/substring( . , 1, 32) ', TERMINATION_PERCENT DECIMAL(5,2) path 'if (name(.) = "Termination") then PercentsTerminated else 1 ', TERMINATION_PAYMENT_DATE VARCHAR(32) path 'if (name(.) = "Termination") then TerminatePayDate/substring(. , 1, 32) else LegData/LegRecordData/TerminationPmtDt/substring(. , 1, 32) ', TERMINATION_DIRTY_PRICE DOUBLE path 'if (name(.) = "Termination") then wsTermDirtyPrice else LegData/LegRecordData/wsTermDirtyPrice ', TERMINATION_ACCRUED_INT DOUBLE path 'if (name(.) = "Termination") then TermInterestAccrued else LegData/LegRecordData/TermInterestAccrued ') as t WHERE t.TRADE_KEY IN (4000054); TRADE_KEY LEG_NUM TERM_NUM TERMINATION_DATE PERC TERMINATION_PAYMENT_DATE 7 ACCR ---------------- -------------------- -------------------- -------------------------------- ---------------- -------------------------------- ------------------------ ------------------------ 4000054 1 1 2011-06-17 50.000 2011-07-08 -2.22649477892227E+006 -2.36111111111110E+004 4000054 1 2 2011-06-17 25.000 2011-07-08 -1.11324738946114E+006 -1.18055555555560E+004 4000054 2 3 2011-06-17 50.000 2011-07-08 +2.50691663177559E+006 +2.65198238579980E+004 4000054 2 4 2011-06-17 25.000 2011-07-08 +1.25345831588780E+006 +1.32599119289990E+004 4000054 1 5 2011-06-17 100.000 2011-07-08 -1.11324738946114E+006 -1.18055555555560E+004 4000054 2 6 2011-06-17 100.000 2011-07-08 +1.25345831588780E+006 +1.32599119289990E+004 6 record(s) selected. </pre>

    Does this solve your problem? Let me know if not.

    There are probably other ways to achieve the same result, but this was the most intuitive I could think of right now.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Wow, this opens a whole new chapter. I did not know we can do something like that.
    Just a minor issue: you see in my original code when I fill in the full termination data I need to check if such data available by checking if TERMINATION_DATE:

    WHERE
    trade.TRADE_KEY IN (input) AND TERMINATION_DATE != ''

    How do we do this check for full termination rows only?

    I have attached a trade that only has partial termination data but no full termination, and it should output the following rows:

    4000055 1 1 2011-06-17 30.0 2011-07-08 -1335896.867353362 -14166.666666667
    4000055 2 2 2011-06-17 30.0 2011-07-08 1504149.979065355 15911.894314799
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-07T07:42:49Z  
    • BingSun
    • ‏2011-07-06T20:23:43Z
    Wow, this opens a whole new chapter. I did not know we can do something like that.
    Just a minor issue: you see in my original code when I fill in the full termination data I need to check if such data available by checking if TERMINATION_DATE:

    WHERE
    trade.TRADE_KEY IN (input) AND TERMINATION_DATE != ''

    How do we do this check for full termination rows only?

    I have attached a trade that only has partial termination data but no full termination, and it should output the following rows:

    4000055 1 1 2011-06-17 30.0 2011-07-08 -1335896.867353362 -14166.666666667
    4000055 2 2 2011-06-17 30.0 2011-07-08 1504149.979065355 15911.894314799
    Hmm, the new document "Vanilla Swap Example.4000055.xml" that you posted looks significantly different than the previous one, as if it belongs to a different XML Schema.
    Many element names are different now, and therefore the XPaths to extract values also need to be different.

    For example, the closest to a termination date is now called <wsTerminationDt> instead of <TerminateDate>, and the value "2011-06-17" that you expect in the output from this document does not exist anywhere in it.
    Instead, the date seems to be given as some (hashed?) Julian date:

    <wsTerminationDt type="struct" spec="struct _WS_JULIAN_DATE" hashval="196861356">

    So, this is a very different animal. Maybe you can elaborate on this?

    In the meantime, let me answer your question in more general terms.
    If you want to apply a row-filter only to the full termination rows, an easy option is to place it in the respective row-generating expression of the XMLTABLE function:

    
    SELECT .... FROM WSD_TRADE_EXPORT trade, XMLTABLE(
    '  ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData[TerminationDt > 
    "" ]/Legs/Leg ) 
    ' passing trade.TRADE_XML as "root" COLUMNS ....
    


    With the predicate TerminationDt > "" in the second row-generating expression, rows for the full termination are produced only if the TerminationDt is greater than the empty string.
    With this I'm applying the filter even before producing a row, while your original WHERE clause applies the filter on the corresponding column after the unwanted row is produced.

    If you have a strong reason for filtering out the rows later, you could generate a column that indicates for each row whether it is a partial or a complete termination, like this:

    
    SELECT t.TRADE_KEY, t.TERM_TYPE, .... FROM WSD_TRADE_EXPORT trade, XMLTABLE(
    '  ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData/Legs/Leg ) 
    ' passing trade.TRADE_XML as "root" COLUMNS TERM_TYPE VARCHAR(16) path 
    'name(.)', ....       TRADE_KEY        TERM_TYPE        LEG_NUM              TERM_NUM             ... ---------------- ---------------- -------------------- -------------------- ... 4000054          Termination                         1                    1 4000054          Termination                         1                    2 4000054          Termination                         2                    3 4000054          Termination                         2                    4 4000054          Leg                                 1                    5 4000054          Leg                                 2                    6   6 record(s) selected.
    


    The XPath function name(.) returns the name of the current element being processed.
    Then your WHERE clause could include the TERM_TYPE column as needed, e.g.:

    WHERE trade.TRADE_KEY IN (input) AND concat(TERM_TYPE,TERMINATION_DATE) != 'Leg';

    But, applying the filtering condition early in the XMLTABLE function seems more elegant and might also perform better. Maybe these ideas are useful for what you need to accomplish?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • BingSun
    BingSun
    7 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-07T14:53:32Z  
    Hmm, the new document "Vanilla Swap Example.4000055.xml" that you posted looks significantly different than the previous one, as if it belongs to a different XML Schema.
    Many element names are different now, and therefore the XPaths to extract values also need to be different.

    For example, the closest to a termination date is now called <wsTerminationDt> instead of <TerminateDate>, and the value "2011-06-17" that you expect in the output from this document does not exist anywhere in it.
    Instead, the date seems to be given as some (hashed?) Julian date:

    <wsTerminationDt type="struct" spec="struct _WS_JULIAN_DATE" hashval="196861356">

    So, this is a very different animal. Maybe you can elaborate on this?

    In the meantime, let me answer your question in more general terms.
    If you want to apply a row-filter only to the full termination rows, an easy option is to place it in the respective row-generating expression of the XMLTABLE function:

    <pre class="jive-pre"> SELECT .... FROM WSD_TRADE_EXPORT trade, XMLTABLE( ' ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData[TerminationDt > "" ]/Legs/Leg ) ' passing trade.TRADE_XML as "root" COLUMNS .... </pre>

    With the predicate TerminationDt > "" in the second row-generating expression, rows for the full termination are produced only if the TerminationDt is greater than the empty string.
    With this I'm applying the filter even before producing a row, while your original WHERE clause applies the filter on the corresponding column after the unwanted row is produced.

    If you have a strong reason for filtering out the rows later, you could generate a column that indicates for each row whether it is a partial or a complete termination, like this:

    <pre class="jive-pre"> SELECT t.TRADE_KEY, t.TERM_TYPE, .... FROM WSD_TRADE_EXPORT trade, XMLTABLE( ' ( $root/ObjectRoot/TradeData/Legs/Leg/LegData/LegRecordData/Terminations/Termination , $root/ObjectRoot/TradeData/Legs/Leg ) ' passing trade.TRADE_XML as "root" COLUMNS TERM_TYPE VARCHAR(16) path 'name(.)', .... TRADE_KEY TERM_TYPE LEG_NUM TERM_NUM ... ---------------- ---------------- -------------------- -------------------- ... 4000054 Termination 1 1 4000054 Termination 1 2 4000054 Termination 2 3 4000054 Termination 2 4 4000054 Leg 1 5 4000054 Leg 2 6 6 record(s) selected. </pre>

    The XPath function name(.) returns the name of the current element being processed.
    Then your WHERE clause could include the TERM_TYPE column as needed, e.g.:

    WHERE trade.TRADE_KEY IN (input) AND concat(TERM_TYPE,TERMINATION_DATE) != 'Leg';

    But, applying the filtering condition early in the XMLTABLE function seems more elegant and might also perform better. Maybe these ideas are useful for what you need to accomplish?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Putting the predicate in the row generating expression is great. And I know the XPath function name(.) is very powerful.

    Thanks you so much for your great help!
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-07T18:42:29Z  
    • BingSun
    • ‏2011-07-07T14:53:32Z
    Putting the predicate in the row generating expression is great. And I know the XPath function name(.) is very powerful.

    Thanks you so much for your great help!
    You're welcome.

    You can find further tips and tricks for XMLTABLE and other types of queries in:


    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • BingSun
    BingSun
    7 Posts

    Re: xmltable: how to number a row based on some value in table

    ‏2011-07-08T16:08:50Z  
    Great resources. I have read the first one. Will go through the second one. Thanks a lot for your time to help me.