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

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
    ACCEPTED ANSWER

    Re: Get First Element in XML Record

    ‏2012-06-06T16:12:42Z  in response to VV4H_mario_fernandes
    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
      ACCEPTED ANSWER

      Re: Get First Element in XML Record

      ‏2012-06-06T17:03:41Z  in response to MatthiasNicola
      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
        ACCEPTED ANSWER

        Re: Get First Element in XML Record

        ‏2012-06-06T17:42:39Z  in response to VV4H_mario_fernandes
        Hi matthias

        After I double checked your query , it is correct.

        Thanks a lot .

        Mario
        • MatthiasNicola
          MatthiasNicola
          321 Posts
          ACCEPTED ANSWER

          Re: Get First Element in XML Record

          ‏2012-06-06T17:57:35Z  in response to VV4H_mario_fernandes
          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/