Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2013
    Posts
    12

    Red face Unanswered: help required on extracting Phonenumber from the XML (CLOB ) column; Spacing issue

    Hi All,
    We are extracting Phonenumber from the XML (CLOB) column. while extracting, it adds extra characters to the Phone number, because of spaces between </areacode> and <Phonenumber>
    Below is the code and ouputs:

    SELECT
    concat(ltrim(rtrim(CHAR(substr(quote_data,locate(' <areaCode>',quote_data)+10,
    (locate('</areaCode>',quote_data))-(locate('<areaCode>',quote_data)+10)))))
    ,ltrim(rtrim(CHAR(substr(quote_data,locate('</areaCode>',quote_data)+41,7))))) as PHONE_NUMBER,SOURCE_INDICATOR FROM TGTAFT.QUOTE as quote
    left outer join
    TGTAFT.reprocessor as reprocessor on
    quote.QUOTE_NUMBER = reprocessor.QUOTE_NUMBER
    and SOURCE_ID<>'AP000MOBILE'
    WHERE locate('<areaCode>',quote_data) > 0

    Output
    PhNumber:
    123er>4567
    123r>45678
    123>456789
    original O/p should be:
    123456789


    Note: we can not take any othertags except <areacode> becuase of uniqueness. remaining all tags are repeating.
    Thanks in advance.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You should provide an XML schema of your document.
    Consider using the built-in xml functions for parsing. Something like this:
    Code:
    select t.acode||t.number
    from table (values 
      clob('<doc>
        <areacode>123</areacode> <Phonenumber>456789</Phonenumber>
      </doc>')
    , clob('<doc>
        <areacode>123</areacode>   <Phonenumber>456789</Phonenumber>
      </doc>')
    ) v(x)
    , xmltable ('$d/doc' passing xmlparse(document v.x) as "d" columns
      "ACODE"  INT PATH 'areacode'
    , "NUMBER" INT PATH 'Phonenumber'
    ) t
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2013
    Posts
    12
    Hi Mark,
    Thanks for the Reply.
    I cannot take phone number because of not having uniqueness (means, already some other node also have phonenumber, which is different).
    here i have provided DBcode o not XML xchema.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Once again: can you provide a sample of your XML document and describe the logic for getting the result from it?
    It's hard to understand the problem only looking at this code...
    Regards,
    Mark.

  5. #5
    Join Date
    Nov 2013
    Posts
    12
    Hi Mark,
    here is the XML code
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <CommercialInsQuoteData xmlns="http://com.farmers.ffq.comm.dm">
    <src>FC000000001</src>
    <landingZipCode>66104</landingZipCode>
    <landingStateCode>KS</landingStateCode>
    <refPersInsQuoteData/>
    <businessInformation>
    <businessType>Apartment</businessType>
    <industry>Apartment</industry>
    <noOfEmployee>1</noOfEmployee>
    <businessName>adsad</businessName>
    <address>
    <city>asdasd</city>
    <state>KS</state>
    <street>sadasd 11</street>
    <zipCode>66104</zipCode>
    </address>
    <comCustomer>
    <firstName>asdasd</firstName>
    <lastName>sadsa</lastName>
    <dob>11/11/1990</dob>
    <preferredLanguage>EN</preferredLanguage>
    <communication>
    <emailId>
    <emailAddress>sadsd@sdas.asd</emailAddress>
    </emailId>
    <phoneNumber>
    <areaCode>111</areaCode>
    <phoneNumber>1111111</phoneNumber>
    </phoneNumber>
    </communication>
    </comCustomer>
    </businessInformation>
    <comAgent>
    <agentId>045391</agentId>
    <assign>true</assign>
    <firstName>Shawn</firstName>
    <lastName>McAllister</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>7940 Parallel Pkwy</street>
    <zipCode>66112-2039</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>smcallister@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9137888288</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>1</reference>
    </comAgent>
    <comAgent>
    <agentId>045324</agentId>
    <firstName>Larry</firstName>
    <lastName>Strait</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>7925 State Ave Ste 105</street>
    <zipCode>66112-2422</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>lstrait@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9132998280</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>2</reference>
    </comAgent>
    <comAgent>
    <agentId>047727</agentId>
    <firstName>Chuck</firstName>
    <lastName>Simpson</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>8247 Leavenworth Rd</street>
    <zipCode>66109-1574</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>csimpson1@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9137883110</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>3</reference>
    </comAgent>
    <comAgent>
    <agentId>040362</agentId>
    <firstName>Dustin</firstName>
    <lastName>Stock</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>100 S 10th St</street>
    <zipCode>66102-5516</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>dstock@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9136213200</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>4</reference>
    </comAgent>
    <comControlData>
    <quoteBrowserVersion>Chrome v33.0.1750.146</quoteBrowserVersion>
    <quoteSessionId>rtlywvLFCpFw49Zm0sDyUiJ</quoteSessionId>
    <lastvisitedpage>businessInfoBean</lastvisitedpage>
    <quoteNo>74853758</quoteNo>
    <quoteName>Commercial</quoteName>
    <agentAssignTimeStamp>2014-03-06 05:41:02.068</agentAssignTimeStamp>
    </comControlData>
    <originIP>127.0.0.1</originIP>
    </CommercialInsQuoteData>

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I'm still missing your algorithm.
    If you need only the phoneNumber and areaCode at the following node:
    CommercialInsQuoteData/businessInformation/comCustomer/communication/phoneNumber
    then if would be:
    Code:
    ...
    , xmltable ('$d/CommercialInsQuoteData/businessInformation/comCustomer/communication/phoneNumber' passing xmlparse(document v.x) as "d" columns
      "ACODE"  INT PATH 'areaCode'
    , "NUMBER" INT PATH 'phoneNumber'
    ) t
    Or do you want to return all phoneNumbers from any node of the document?
    Regards,
    Mark.

  7. #7
    Join Date
    Nov 2013
    Posts
    12
    Hi Mark,
    May be i didnt paste the comple SQL code.
    Here the complete SQL Code. please have a look.

    SELECT quote.QUOTE_NUMBER
    ,SOURCE_ID
    ,quote.LOB
    ,quote.AUTO_COMPANY_CODE
    ,QUOTE_INIT_TMSTMP
    ,DATE( QUOTE_INIT_TMSTMP ) AS QUOTE_INIT_DATE,TIME( QUOTE_INIT_TMSTMP ) AS QUOTE_INIT_TIME,QUOTE_UPDATE_TMSTMP
    ,DATE( QUOTE_UPDATE_TMSTMP ) AS QUOTE_UPDATE_DATE,TIME( QUOTE_UPDATE_TMSTMP ) AS QUOTE_UPDATE_TIME
    ,BOUND_IND
    ,HOME_BOUND_IND
    ,LIFE_BOUND_IND
    ,CHANGE_IND
    ,UPLOAD_IND
    ,ASSIGNED_AGENT
    ,ASSIGNED_PRODUCER
    ,QUALIFIED_AGENTS
    ,QUALIFIED_PRODUCERS
    ,AGT_ASSIGN_TMSTMP
    ,DATE( AGT_ASSIGN_TMSTMP ) AS AGT_ASSIGN_DATE
    ,TIME( AGT_ASSIGN_TMSTMP ) AS AGT_ASSIGN_TIME
    ,AGT_DEL_IND
    ,AGT_CHANGE_IND
    ,BUY_INITIATED
    ,FIRST_NAME
    ,LAST_NAME
    ,PSEUDO_QUOTE_AUTO
    ,PSEUDO_QUOTE_FIRE
    ,AUTO_PREMIUM_AMT
    ,HOME_PREMIUM_AMT
    ,LIFE_PREMIUM_AMT
    ,QUOTE_EFF_DT
    ,HOME_EFF_DT
    ,LIFE_EFF_DT
    ,STATE
    ,ZIP
    ,DOB
    ,EMAIL
    ,ADE_ALERT_STATUS
    ,EMAIL_UPDATES
    ,HOUSEHOLD_NUM
    ,LAST_VISITED_PAGE
    ,ORIGIN_IP
    ,MILESTONE_ID
    ,RETRY_COUNT
    ,concat(ltrim(rtrim(CHAR(substr(quote_data,locate( '<areaCode>',quote_data)+10,
    (locate('</areaCode>',quote_data))-(locate('<areaCode>',quote_data)+10)))))
    ,ltrim(rtrim(CHAR(substr(quote_data,locate('</areaCode>',quote_data)+42,7))))) as PHONE_NUMBER,SOURCE_INDICATOR FROM TGTFT.QUOTE as quote
    left outer join
    TGTAFT.reprocessor as reprocessor on
    quote.QUOTE_NUMBER = reprocessor.QUOTE_NUMBER
    and SOURCE_ID<>'AP000MOBILE'
    WHERE locate('<areaCode>',quote_data) > 0
    order by QUOTE_INIT_TMSTMP desc FETCH FIRST 500 ROWS ONLY WITH UR



    Your help is greatly appreciated.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    If we are trying to resolve the problem with a phoneNumber extraction, then this whole sql statement doesn't help us. I need your logic for the phoneNumber extraction.
    I see that you have a number of nodes named 'phoneNumber' in your xml document.

    So my question was: do you need to extract only one of them (I anticipated that it could be one from the CommercialInsQuoteData/businessInformation/comCustomer/communication/phoneNumber node) or all of them from each 'phoneNumber' node (from each of the CommercialInsQuoteData/comAgent/communication/phoneNumber nodes in addition to the previous)?
    Regards,
    Mark.

  9. #9
    Join Date
    Nov 2013
    Posts
    12
    Yes Mark, You are correct!.
    I want o extract only phonenumber, that to 'phonenumber' present in '<ComCustomer> 'node.
    More specifically, i want to extract <areacode> and <phonenumber> from <COmcustomer>Node, to display output like below

    Output:
    111-1111111

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If you have your CLOB value as you specified earlier, then this should work:
    Code:
    select ...
    , t.acode||'-'||t.number as phonenumber
    FROM TGTFT.QUOTE as quote
    left outer join TGTAFT.reprocessor as reprocessor 
    on quote.QUOTE_NUMBER = reprocessor.QUOTE_NUMBER and SOURCE_ID<>'AP000MOBILE'
    join xmltable (
    XMLNAMESPACES( DEFAULT 'http://com.farmers.ffq.comm.dm' ),
    '$d/CommercialInsQuoteData/businessInformation/comCustomer/communication/phoneNumber' passing xmlparse(document quote_data) as "d" 
    columns
      "ACODE"  VARCHAR(10) PATH 'areaCode'
    , "NUMBER" VARCHAR(20) PATH 'phoneNumber'
    ) t on 1=1
    order by QUOTE_INIT_TMSTMP desc 
    FETCH FIRST 500 ROWS ONLY WITH UR
    Regards,
    Mark.

  11. #11
    Join Date
    Nov 2013
    Posts
    12
    Hi Mark,
    The above you mentioned query is not displaying anything showing error.

    we are storing XML data in the " Quote_data" column of the same table.
    From that column ,we are locating position of <areacode> tag, because of uniqueness.
    our query is executing fine for some quotes, but some other quotes ,its adding some characters.its because of the space between </areacode> and <phonenumber>. May i know how to trim those sapces.

    your help will be greatly appreciated.

  12. #12
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    My last query was not intended to be run "as is" - I omitted many of your columns at the beginning of the column list. If you want to run it - remove the following from the text: '... ,'. And if this statement doesn't work, then please, provide an exact error text.
    I can't run your exact statement since I don't have your tables, but you can run the following "as is" and this should work:
    Code:
    select t.acode||'-'||t.number as phonenumber
    from table (values 
      clob('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <CommercialInsQuoteData xmlns="http://com.farmers.ffq.comm.dm">
    <src>FC000000001</src>
    <landingZipCode>66104</landingZipCode>
    <landingStateCode>KS</landingStateCode>
    <refPersInsQuoteData/>
    <businessInformation>
    <businessType>Apartment</businessType>
    <industry>Apartment</industry>
    <noOfEmployee>1</noOfEmployee>
    <businessName>adsad</businessName>
    <address>
    <city>asdasd</city>
    <state>KS</state>
    <street>sadasd 11</street>
    <zipCode>66104</zipCode>
    </address>
    <comCustomer>
    <firstName>asdasd</firstName>
    <lastName>sadsa</lastName>
    <dob>11/11/1990</dob>
    <preferredLanguage>EN</preferredLanguage>
    <communication>
    <emailId>
    <emailAddress>sadsd@sdas.asd</emailAddress>
    </emailId>
    <phoneNumber>
    <areaCode>111</areaCode>
    <phoneNumber>1111111</phoneNumber>
    </phoneNumber>
    </communication>
    </comCustomer>
    </businessInformation>
    <comAgent>
    <agentId>045391</agentId>
    <assign>true</assign>
    <firstName>Shawn</firstName>
    <lastName>McAllister</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>7940 Parallel Pkwy</street>
    <zipCode>66112-2039</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>smcallister@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9137888288</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>1</reference>
    </comAgent>
    <comAgent>
    <agentId>045324</agentId>
    <firstName>Larry</firstName>
    <lastName>Strait</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>7925 State Ave Ste 105</street>
    <zipCode>66112-2422</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>lstrait@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9132998280</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>2</reference>
    </comAgent>
    <comAgent>
    <agentId>047727</agentId>
    <firstName>Chuck</firstName>
    <lastName>Simpson</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>8247 Leavenworth Rd</street>
    <zipCode>66109-1574</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>csimpson1@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9137883110</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>3</reference>
    </comAgent>
    <comAgent>
    <agentId>040362</agentId>
    <firstName>Dustin</firstName>
    <lastName>Stock</lastName>
    <address>
    <city>Kansas City</city>
    <state>KS</state>
    <street>100 S 10th St</street>
    <zipCode>66102-5516</zipCode>
    </address>
    <communication>
    <emailId>
    <emailAddress>dstock@farmersagent.com</emailAddress>
    </emailId>
    <phoneNumber>
    <phoneNumber>9136213200</phoneNumber>
    </phoneNumber>
    </communication>
    <reference>4</reference>
    </comAgent>
    <comControlData>
    <quoteBrowserVersion>Chrome v33.0.1750.146</quoteBrowserVersion>
    <quoteSessionId>rtlywvLFCpFw49Zm0sDyUiJ</quoteSessionId>
    <lastvisitedpage>businessInfoBean</lastvisitedpage>
    <quoteNo>74853758</quoteNo>
    <quoteName>Commercial</quoteName>
    <agentAssignTimeStamp>2014-03-06 05:41:02.068</agentAssignTimeStamp>
    </comControlData>
    <originIP>127.0.0.1</originIP>
    </CommercialInsQuoteData>')
    ) v(x)
    , xmltable (
    XMLNAMESPACES( DEFAULT 'http://com.farmers.ffq.comm.dm' ),
    '$d/CommercialInsQuoteData/businessInformation/comCustomer/communication/phoneNumber' passing xmlparse(document v.x) as "d" 
    columns
      "ACODE"  VARCHAR(10) PATH 'areaCode'
    , "NUMBER" VARCHAR(20) PATH 'phoneNumber'
    ) t
    This is an example on your sample data provided earlier.
    Regards,
    Mark.

  13. #13
    Join Date
    Nov 2013
    Posts
    12
    Hi Mark!
    Its working Perfect.
    But the thing is , i want to automate it.. like client entered data is storing in XML format in the above format, from there i need to pick the phone number. Any suggestion on this Please.

  14. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by vasu_479 View Post
    Its working Perfect.
    But the thing is , i want to automate it.. like client entered data is storing in XML format in the above format, from there i need to pick the phone number. Any suggestion on this Please.
    What do you mean saying "to automate it"?
    Do you ask how to write an application which saves the client entered data to some table?
    Regards,
    Mark.

  15. #15
    Join Date
    Nov 2013
    Posts
    12
    exactly mark!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •