Topic
  • 4 replies
  • Latest Post - ‏2012-06-06T17:57:35Z by MatthiasNicola
VV4H_mario_fernandes
43 Posts

Pinned topic Get First Element in XML Record

‏2012-06-06T15:38:23Z |
Hi

In this query below I am getting all the contact elements that have
contactRoleAtClient="Confirmation Officer"

How can I get only the first contact element thas has contactRoleAtClient="Confirmation Officer"


client_id,  FIRSTNAME , LASTNAME,title,phoneNumber,emailAddress FROM KASPER.XML_CLIENT C, XMLTABLE(
'$d/kasperSchema/TDSClient/kasperClient/client/contact/contactRoleAtClient[text()="Confirmation Officer" ]' PASSING C.XML_CLIENT AS 
"d" COLUMNS Client_ID   INTEGER  PATH 
'../../@id' , FIRSTNAME varchar(10000) PATH 
'../firstName/text()', LASTNAME  varchar(10000) PATH 
'../lastName/text()', title  VARCHAR(100) PATH 
'../title/text()', phoneNumber   VARCHAR(50) PATH 
'../phoneNumber/text()', emailAddress   VARCHAR(100) PATH 
'../emailAddress/text()' ) AS X ) CONTACT ON KASPER_CLIENT.CLIENTID = CONTACT.CLIENT_ID
Updated on 2012-06-06T17:57:35Z at 2012-06-06T17:57:35Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Get First Element in XML Record

    ‏2012-06-06T16:12:42Z  
    Hi Mario,

    I think you could use a positional predicate to get just the first of the matching elements:

    
    XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/contact/contactRoleAtClient[text()="Confirmation Officer" ][1]' PASSING C.XML_CLIENT AS 
    "d"
    


    BTW, you don't even need to use /text(). If there is only one <contactRoleAtClient> per <contact> then you can further simplify the query and avoid some of the parent steps, like this:

    
    FROM KASPER.XML_CLIENT C, XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/contact[contactRoleAtClient ="Confirmation Officer" ][1]' PASSING C.XML_CLIENT AS 
    "d" COLUMNS Client_ID     INTEGER        PATH 
    '../@id' , FIRSTNAME     varchar(10000) PATH 
    'firstName', LASTNAME      varchar(10000) PATH 
    'lastName', title         VARCHAR(100)   PATH 
    'title', phoneNumber   VARCHAR(50)    PATH 
    'phoneNumber', emailAddress  VARCHAR(100)   PATH 
    'emailAddress' ) AS X
    


    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: Get First Element in XML Record

    ‏2012-06-06T17:03:41Z  
    Hi Mario,

    I think you could use a positional predicate to get just the first of the matching elements:

    <pre class="jive-pre"> XMLTABLE( '$d/kasperSchema/TDSClient/kasperClient/client/contact/contactRoleAtClient[text()="Confirmation Officer" ][1]' PASSING C.XML_CLIENT AS "d" </pre>

    BTW, you don't even need to use /text(). If there is only one <contactRoleAtClient> per <contact> then you can further simplify the query and avoid some of the parent steps, like this:

    <pre class="jive-pre"> FROM KASPER.XML_CLIENT C, XMLTABLE( '$d/kasperSchema/TDSClient/kasperClient/client/contact[contactRoleAtClient ="Confirmation Officer" ][1]' PASSING C.XML_CLIENT AS "d" COLUMNS Client_ID INTEGER PATH '../@id' , FIRSTNAME varchar(10000) PATH 'firstName', LASTNAME varchar(10000) PATH 'lastName', title VARCHAR(100) PATH 'title', phoneNumber VARCHAR(50) PATH 'phoneNumber', emailAddress VARCHAR(100) PATH 'emailAddress' ) AS X </pre>

    Does this help?

    Matthias

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

    The query you gave me returns both the contacts below.
    I only want the first one.
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client id = "64604">

    <legalEntity>
    <legalName>SDASD12123</legalName>
    </legalEntity>
    <contact id = "1">
    <contactRoleAtClient>Confirmation Officer</contactRoleAtClient>
    <firstName>suraj</firstName>
    <lastName>ddd</lastName>
    <phoneNumber>232322444</phoneNumber>
    <emailAddress>ddd@sap.com</emailAddress>
    <title>Confirmation Officer</title>
    </contact>
    <contact id = "2">
    <contactRoleAtClient>Confirmation Officer</contactRoleAtClient>
    <firstName>ddd</firstName>
    <lastName>www</lastName>
    <phoneNumber>11212111</phoneNumber>
    <emailAddress>sss@sap.com</emailAddress>
    <title>Confirmation Officer</title>
    </contact>
    </client>

    </kasperClient>
    </TDSClient>
    </kasperSchema>
  • VV4H_mario_fernandes
    43 Posts

    Re: Get First Element in XML Record

    ‏2012-06-06T17:42:39Z  
    Hi matthias

    The query you gave me returns both the contacts below.
    I only want the first one.
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client id = "64604">

    <legalEntity>
    <legalName>SDASD12123</legalName>
    </legalEntity>
    <contact id = "1">
    <contactRoleAtClient>Confirmation Officer</contactRoleAtClient>
    <firstName>suraj</firstName>
    <lastName>ddd</lastName>
    <phoneNumber>232322444</phoneNumber>
    <emailAddress>ddd@sap.com</emailAddress>
    <title>Confirmation Officer</title>
    </contact>
    <contact id = "2">
    <contactRoleAtClient>Confirmation Officer</contactRoleAtClient>
    <firstName>ddd</firstName>
    <lastName>www</lastName>
    <phoneNumber>11212111</phoneNumber>
    <emailAddress>sss@sap.com</emailAddress>
    <title>Confirmation Officer</title>
    </contact>
    </client>

    </kasperClient>
    </TDSClient>
    </kasperSchema>
    Hi matthias

    After I double checked your query , it is correct.

    Thanks a lot .

    Mario
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Get First Element in XML Record

    ‏2012-06-06T17:57:35Z  
    Hi matthias

    After I double checked your query , it is correct.

    Thanks a lot .

    Mario
    Ok, good :-).

    I just checked it too:

    
    create table XML_CLIENT(XML_CLIENT XML);   insert into xml_client values(
    ' <kasperSchema> <TDSClient>   <kasperClient> <client id = 
    "64604">   <legalEntity> <legalName>SDASD12123</legalName> </legalEntity> <contact id = 
    "1"> <contactRoleAtClient>Confirmation Officer</contactRoleAtClient> <firstName>suraj</firstName> <lastName>ddd</lastName> <phoneNumber>232322444</phoneNumber> <emailAddress>ddd@sap.com</emailAddress> <title>Confirmation Officer</title> </contact> <contact id = 
    "2"> <contactRoleAtClient>Confirmation Officer</contactRoleAtClient> <firstName>ddd</firstName> <lastName>www</lastName> <phoneNumber>11212111</phoneNumber> <emailAddress>sss@sap.com</emailAddress> <title>Confirmation Officer</title> </contact> </client>   </kasperClient> </TDSClient> </kasperSchema>
    ');     SELECT X.* FROM XML_CLIENT C, XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/contact[contactRoleAtClient ="Confirmation Officer" ][1]' PASSING C.XML_CLIENT AS 
    "d" COLUMNS Client_ID     INTEGER     PATH 
    '../@id' , FIRSTNAME     varchar(20) PATH 
    'firstName', LASTNAME      varchar(20) PATH 
    'lastName', Title         VARCHAR(20) PATH 
    'title', phoneNumber   VARCHAR(20) PATH 
    'phoneNumber', emailAddress  VARCHAR(35) PATH 
    'emailAddress' ) AS X   CLIENT_ID   FIRSTNAME            LASTNAME             TITLE                PHONENUMBER          EMAILADDRESS ----------- -------------------- -------------------- -------------------- -------------------- ----------------------------------- 64604 suraj                ddd                  Confirmation Officer 232322444            ddd@sap.com   1 record(s) selected.       SELECT X.* FROM XML_CLIENT C, XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/contact[contactRoleAtClient ="Confirmation Officer" ]' PASSING C.XML_CLIENT AS 
    "d" COLUMNS Client_ID     INTEGER     PATH 
    '../@id' , FIRSTNAME     varchar(20) PATH 
    'firstName', LASTNAME      varchar(20) PATH 
    'lastName', Title         VARCHAR(20) PATH 
    'title', phoneNumber   VARCHAR(20) PATH 
    'phoneNumber', emailAddress  VARCHAR(35) PATH 
    'emailAddress' ) AS X; CLIENT_ID   FIRSTNAME            LASTNAME             TITLE                PHONENUMBER          EMAILADDRESS ----------- -------------------- -------------------- -------------------- -------------------- ----------------------------------- 64604 suraj                ddd Confirmation Officer 232322444            ddd@sap.com 64604 ddd                  www                  Confirmation Officer 11212111             sss@sap.com   2 record(s) selected.
    



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