Topic
  • 11 replies
  • Latest Post - ‏2012-03-20T09:52:40Z by ghostrider2
ghostrider2
ghostrider2
8 Posts

Pinned topic Insert values from xquery into db column

‏2012-03-05T13:53:21Z |
Hi , i have retrieved required values in variables $req_val1 & $req_val


xquery declare namespace xbrli=
"http://www.xbrl.org/2003/instance";declare namespace in-gp=
"http://www.xbrl.org/in/2011-03- 31/in-gp
"; for $y in db2-fn:sqlquery('select XML from XMLTEST') let $c := $y/xbrli:xbrl/xbrli:context/xbrli:period/xbrli:in stant  

for $counter in (1 to count($c)) let $contextValinDoc := $y/xbrli:xbrl/xbrli:context[$counter ]/@id let $isDesiredCont ext :=

boolean($y/xbrli:xbrl/xbrli:context[@id=$contextValinDoc]/xbrli:period[xbrli:instant = 
'2011-03-31']) let $req_val := i f ($isDesiredContext) then $y/xbrli:xbrl/in-gp:PercentageHeld[@contextRef=$cont extValinDoc]/text() 

else() let $req_val1 :=  

if ($isDesiredContext) then $y/xbrli:xbrl/in-gp:PaidCap[@ contextRef=$contextValinDoc]/text() 

else () 

return  ( <a> 
{$req_val1
}</a> , <b> 
{$req_val 
}</b> ) ;

Now i want to insert them into a temp table in db2 as integers.
How can i do that ?
Thanks
Updated on 2012-03-20T09:52:40Z at 2012-03-20T09:52:40Z by ghostrider2
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-05T21:10:13Z  
    Here is an example that shows the general idea. This example uses XML from the DB2 sample database.

    Let's assume this is my existing XQuery:

    
    xquery declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    'SELECT info FROM customer') let $id := $y/customerinfo/@Cid let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>;
    


    And I want to insert the two extracted values into this table:

    CREATE TABLE mytable(c1 INTEGER, c2 INTEGER);

    I could use the following SQL/XML statement:

    
    INSERT INTO mytable SELECT X.* FROM XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>
    ' COLUMNS c1  INTEGER PATH 
    'a', c2  INTEGER PATH 
    'b') AS X;
    


    Note the use of double quotes inside the db2-fn:sqlquery function, and note that the return clause constructs a single result element <out> per iteration.

    Does that help?


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-06T07:26:23Z  
    Here is an example that shows the general idea. This example uses XML from the DB2 sample database.

    Let's assume this is my existing XQuery:

    <pre class="jive-pre"> xquery declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( 'SELECT info FROM customer') let $id := $y/customerinfo/@Cid let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out>; </pre>

    And I want to insert the two extracted values into this table:

    CREATE TABLE mytable(c1 INTEGER, c2 INTEGER);

    I could use the following SQL/XML statement:

    <pre class="jive-pre"> INSERT INTO mytable SELECT X.* FROM XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out> ' COLUMNS c1 INTEGER PATH 'a', c2 INTEGER PATH 'b') AS X; </pre>

    Note the use of double quotes inside the db2-fn:sqlquery function, and note that the return clause constructs a single result element <out> per iteration.

    Does that help?


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi,
    Thanks , that helps a lot :)
    There are two things i am still having a problem with :
    1)Can i insert data along with this from another table (non-xml) (eg there is a column c3 whose value is to be taken from another table t1 and inserted into this table.) .
    I have been trying to find an example for this but haven't been able to .

    2) I would only want to enter values in the table if '$id' and '$c' return a value , as of now empty values are also being entered, which gives me a error "can't cast to INT"

    Thanks
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-06T08:35:52Z  
    Hi,
    Thanks , that helps a lot :)
    There are two things i am still having a problem with :
    1)Can i insert data along with this from another table (non-xml) (eg there is a column c3 whose value is to be taken from another table t1 and inserted into this table.) .
    I have been trying to find an example for this but haven't been able to .

    2) I would only want to enter values in the table if '$id' and '$c' return a value , as of now empty values are also being entered, which gives me a error "can't cast to INT"

    Thanks
    Hi ghostrider,

    as for your question 1), yes, you could change the subselect in the insert statement to include a join with another table.

    The following example starts with the query from my previous post, then creates another table, and then performs the join:

    
    SELECT X.* FROM XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>
    ' COLUMNS c1  INTEGER PATH 
    'a', c2  INTEGER PATH 
    'b') AS X;   C1          C2 ----------- ----------- 1000           1 1001           1 1002           1 1003           4 1004           2 1005           2   6 record(s) selected.     CREATE TABLE source(c1 INTEGER, c3 VARCHAR(32)); INSERT INTO source VALUES(1001, 
    'Thousandandone'); INSERT INTO source VALUES(1003, 
    'Thousandandthree');   SELECT X.*, S.c3 FROM source S, XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>
    ' COLUMNS c1  INTEGER PATH 
    'a', c2  INTEGER PATH 
    'b') AS X WHERE X.c1 = S.c1;   C1          C2          C3 ----------- ----------- -------------------------------- 1001           1 Thousandandone 1003           4 Thousandandthree   2 record(s) selected.
    


    Now you just have to add a proper INSERT clause at the top of this statement. And you could certainly change the join to an outer join, if needed.

    For your question 2), there are many possible solutions. For example, you can avoid the error of casting an empty sequence to integer if you generate a default value (such as NULL or zero) whenever there is an empty sequence. This can easily be done in the COLUMNS clause of the XMLTABLE function:

    
    SELECT X.* FROM XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>
    ' COLUMNS c1  INTEGER DEFAULT 0 PATH 
    'a/text()', c2  INTEGER DEFAULT 0 PATH 
    'b/text()') AS X; SELECT X.* FROM XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out>
    ' COLUMNS c1  INTEGER DEFAULT NULL PATH 
    'a/text()', c2  INTEGER DEFAULT NULL PATH 
    'b/text()') AS X;
    

    Another option is to use an if-then-else in the return clause to not even generate these unwanted rows:

    
    SELECT X.* FROM XMLTABLE(
    '  declare namespace xbrli=
    "http://www.xbrl.org/2003/instance"; 
    
    for $y in db2-fn:sqlquery(
    "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) 
    
    return 
    
    if (fn:exists($id) and fn:exists($c)) then <out><a>
    {$id
    }</a><b>
    {$c
    }</b></out> 
    
    else ()
    ' COLUMNS c1  INTEGER PATH 
    'a', c2  INTEGER PATH 
    'b') AS X;
    


    Note that the XMLTABLE function produces one row for each item in the result of the XQuery expression that's inside the XMLTABLE function. If there is a customer document in my table for which the XQuery expression returns an empty sequence, such as "else ()", then no row is generated.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-15T06:50:14Z  
    Thanks :)
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T08:00:06Z  
    Hi ghostrider,

    as for your question 1), yes, you could change the subselect in the insert statement to include a join with another table.

    The following example starts with the query from my previous post, then creates another table, and then performs the join:

    <pre class="jive-pre"> SELECT X.* FROM XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out> ' COLUMNS c1 INTEGER PATH 'a', c2 INTEGER PATH 'b') AS X; C1 C2 ----------- ----------- 1000 1 1001 1 1002 1 1003 4 1004 2 1005 2 6 record(s) selected. CREATE TABLE source(c1 INTEGER, c3 VARCHAR(32)); INSERT INTO source VALUES(1001, 'Thousandandone'); INSERT INTO source VALUES(1003, 'Thousandandthree'); SELECT X.*, S.c3 FROM source S, XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out> ' COLUMNS c1 INTEGER PATH 'a', c2 INTEGER PATH 'b') AS X WHERE X.c1 = S.c1; C1 C2 C3 ----------- ----------- -------------------------------- 1001 1 Thousandandone 1003 4 Thousandandthree 2 record(s) selected. </pre>

    Now you just have to add a proper INSERT clause at the top of this statement. And you could certainly change the join to an outer join, if needed.

    For your question 2), there are many possible solutions. For example, you can avoid the error of casting an empty sequence to integer if you generate a default value (such as NULL or zero) whenever there is an empty sequence. This can easily be done in the COLUMNS clause of the XMLTABLE function:

    <pre class="jive-pre"> SELECT X.* FROM XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out> ' COLUMNS c1 INTEGER DEFAULT 0 PATH 'a/text()', c2 INTEGER DEFAULT 0 PATH 'b/text()') AS X; SELECT X.* FROM XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return <out><a> {$id }</a><b> {$c }</b></out> ' COLUMNS c1 INTEGER DEFAULT NULL PATH 'a/text()', c2 INTEGER DEFAULT NULL PATH 'b/text()') AS X; </pre>
    Another option is to use an if-then-else in the return clause to not even generate these unwanted rows:

    <pre class="jive-pre"> SELECT X.* FROM XMLTABLE( ' declare namespace xbrli= "http://www.xbrl.org/2003/instance"; for $y in db2-fn:sqlquery( "SELECT info FROM customer") let $id := $y/customerinfo/data(@Cid) let $c := count($y/customerinfo/phone) return if (fn:exists($id) and fn:exists($c)) then <out><a> {$id }</a><b> {$c }</b></out> else () ' COLUMNS c1 INTEGER PATH 'a', c2 INTEGER PATH 'b') AS X; </pre>

    Note that the XMLTABLE function produces one row for each item in the result of the XQuery expression that's inside the XMLTABLE function. If there is a customer document in my table for which the XQuery expression returns an empty sequence, such as "else ()", then no row is generated.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias

    I have a table in which i have a primary key (non-xml) & a corresponding XML stored.
    When i create a new table from the data extracted from the XML , i need to insert the primary key as well to map the data with the xml it is from.

    I tried something like :

    SELECT XML_IDENTIFIER , X.* FROM TABLE_1 , XMLTABLE ('xquery here') AS X;

    But this gives me all 'XML_IDENTIFIER' with the records retrieved from XMLTABLE without a proper mapping.
    Since XMLTABLE can return zero or more than one record for the same XML document.

    Is there a way i can retrieve the field XML_IDENTIFIER from the table TABLE_1 from inside the XMLTABLE

    Thanks
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T08:00:47Z  
    additions to the initial question !
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T08:38:11Z  
    Hi Matthias

    I have a table in which i have a primary key (non-xml) & a corresponding XML stored.
    When i create a new table from the data extracted from the XML , i need to insert the primary key as well to map the data with the xml it is from.

    I tried something like :

    SELECT XML_IDENTIFIER , X.* FROM TABLE_1 , XMLTABLE ('xquery here') AS X;

    But this gives me all 'XML_IDENTIFIER' with the records retrieved from XMLTABLE without a proper mapping.
    Since XMLTABLE can return zero or more than one record for the same XML document.

    Is there a way i can retrieve the field XML_IDENTIFIER from the table TABLE_1 from inside the XMLTABLE

    Thanks
    Hi ghostrider,

    I'm not sure I fully understand your question. In particular, I'm not clear what you mean by "this gives me all XML_IDENTIFIER with the records retrieved from XMLTABLE without a proper mapping."

    For each XML document from which you extract values, the relational id of the row will be repeated for each value extract value, which is a proper mapping, like this:

    
    create table ghost(xml_identifier INT, doc XML);   insert into ghost values(1, 
    '<a><b>55</b><b>66</b></a>'); insert into ghost values(2, 
    '<a><b>77</b></a>');   select xml_identifier, X.* from ghost , XMLTABLE(
    '$DOC/a/b' COLUMNS b  integer  path 
    '.') X;   XML_IDENTIFIER B -------------- ----------- 1          55 1          66 2          77   3 record(s) selected.
    


    Can you modify this example or provide a different example that shows a result "without proper mapping"?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T10:49:55Z  
    Hi ghostrider,

    I'm not sure I fully understand your question. In particular, I'm not clear what you mean by "this gives me all XML_IDENTIFIER with the records retrieved from XMLTABLE without a proper mapping."

    For each XML document from which you extract values, the relational id of the row will be repeated for each value extract value, which is a proper mapping, like this:

    <pre class="jive-pre"> create table ghost(xml_identifier INT, doc XML); insert into ghost values(1, '<a><b>55</b><b>66</b></a>'); insert into ghost values(2, '<a><b>77</b></a>'); select xml_identifier, X.* from ghost , XMLTABLE( '$DOC/a/b' COLUMNS b integer path '.') X; XML_IDENTIFIER B -------------- ----------- 1 55 1 66 2 77 3 record(s) selected. </pre>

    Can you modify this example or provide a different example that shows a result "without proper mapping"?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi,
    Thanks for your reply.

    eg:

    I have a table like

    Column Type Type
    name schema name
    ---------
    XML_ID SYSIBM INTEGER
    XMLS SYSIBM XML
    The records are
    1 45
    2 53

    The XQuery i write to get value of b is :
    SELECT X.* FROM XMLTABLE('for $y in db2-fn:sqlquery("select XMLS from xmls") let $val := $y/a/b return <out> {$val} </out> ' COLUMNS c1 VARCHAR(20) PATH 'a') AS X;
    and the result is :
    XML_ID C1

    --------------------
    1 45
    2 45
    1 53
    2 53
    whereas i expect something like

    XML_ID C1

    ------------
    1 45
    2 53
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T11:49:41Z  
    Hi,
    Thanks for your reply.

    eg:

    I have a table like

    Column Type Type
    name schema name
    ---------
    XML_ID SYSIBM INTEGER
    XMLS SYSIBM XML
    The records are
    1 45
    2 53

    The XQuery i write to get value of b is :
    SELECT X.* FROM XMLTABLE('for $y in db2-fn:sqlquery("select XMLS from xmls") let $val := $y/a/b return <out> {$val} </out> ' COLUMNS c1 VARCHAR(20) PATH 'a') AS X;
    and the result is :
    XML_ID C1

    --------------------
    1 45
    2 45
    1 53
    2 53
    whereas i expect something like

    XML_ID C1

    ------------
    1 45
    2 53
    My post had been formated by the editor :

    The records are
    
    1 <a> <b id=
    "new">45</b> </a> 2 <a> <b id=
    "old">53</b> </a>
    
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-17T21:07:41Z  
    My post had been formated by the editor :

    The records are
    <pre class="jive-pre"> 1 <a> <b id= "new">45</b> </a> 2 <a> <b id= "old">53</b> </a> </pre>
    Ok, now I understand.

    Since you have the relational XML_ID column in the result set I'm sure your query was slightly different that what you posted. It must have included the column name in the select list and the table name in the FROM clause, like this:

    
    create table ghost(xml_identifier INT, doc XML);   insert into ghost values(1, 
    '<a><b id="new">45</b></a>'); insert into ghost values(2, 
    '<a><b id="old">53</b></a>');   SELECT xml_identifier, X.* FROM ghost, XMLTABLE(
    'for $y in db2-fn:sqlquery("select doc from ghost")  let $val := $y/a/b 
    
    return <out>
    {$val
    }</out> 
    '  COLUMNS c1 VARCHAR(20) PATH 
    'b') AS X;   XML_IDENTIFIER C1 -------------- -------------------- 1 45 1 53 2 45 2 53   4 record(s) selected.
    


    And yes, that's not the result you want because it is a Cartesian product between the table "ghost" and the table produced by the XMLTABLE function. Since the query specifies "FROM ghost, XMLTABLE(...)", the XMLTABLE function is called once for every row of the table ghost, like it should be. But, for each row in ghost the XMLTABLE function in this example executes db2-fn:sqlquery("select doc from ghost") which produces rows for all documents in the XML column. This is where the multiplication (Cartesian product) comes from.

    The recommended way to solve the problem is to change the XMLTABLE function so that only one document is processed per row of the table ghost. In the following query I have replaced db2-fn:sqlquery() with $DOC, which is a reference to the XML columns in ghost, which essentially establishes a join condition between ghost and the table produced by XMLTABLE:

    
    SELECT xml_identifier, X.* FROM ghost, XMLTABLE(
    'for $y in $DOC let $val := $y/a/b 
    
    return <out>
    {$val
    }</out> 
    '  COLUMNS c1 VARCHAR(20) PATH 
    'b') AS X; XML_IDENTIFIER C1 -------------- -------------------- 1 45 2 53   2 record(s) selected.
    

    If you really wanted, you could also code a more explicit join condition. In the next query, $XML_IDENTIFIER refers to the relational column in table ghost, which is passed as a parameter into the db2-fn:sqlquery function. This works, but is more convoluted than necessary:

    
    SELECT xml_identifier, X.* FROM ghost, XMLTABLE(
    'for $y in db2-fn:sqlquery(" select doc from ghost g where g.xml_identifier = parameter(1)
    ", $XML_IDENTIFIER)  let $val := $y/a/b 
    
    return <out>
    {$val
    }</out> 
    '  COLUMNS c1 VARCHAR(20) PATH 
    'b') AS X;   XML_IDENTIFIER C1 -------------- -------------------- 1 45 2 53   2 record(s) selected.
    


    Does that help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • ghostrider2
    ghostrider2
    8 Posts

    Re: Insert values from xquery into db column

    ‏2012-03-20T09:52:40Z  
    Thanks
    It sure does help a lot :)