Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    29

    Unanswered: XLMQUERY to extract field from string

    Hello,
    I'm trying to write a function that needs to extract a value from a string containing an XML document. I don't have XML columns in my DB but just strings also for XML, for historical reasons.
    I don't have much experience with XMLQUERY so I would code my function like this, but it doesn't compile:

    create function extractField
    (in p_fieldName varchar(8000),
    in p_fieldNumber integer,
    in p_fieldsXml clob
    )
    returns clob
    begin
    return XMLCAST(XMLQUERY( '$FLD/ObjectFields[Name=$FLD_NAME,Number=$FLD_NUMBER]/Contents[1]'
    PASSING p_fieldsXml AS "FLD",
    p_fieldName AS "FLD_NAME",
    p_fieldNumber AS "FLD_NUMBER") as clob) ;
    end

    For example, if you pass the string
    '<?xml version="1.0" encoding="UTF-8"?>
    <Object>
    <Fields>
    <Name>Name</Name>
    <Number>0</Number>
    <Contents>DNS Name</Contents>
    </Fields>
    <Fields>
    <Name>SMIME</Name>
    <Number>0</Number>
    <Contents>My content</Contents>
    </Fields>
    </Object>'
    as p_fieldsXml,
    'Name' as p_fieldName and
    0 as p_fieldNumber

    the output of the function should be: 'DNS Name'

    Could you please suggest the correct implementation for this?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Before trying to compile a function, make sure your standalone expression works. You can use the VALUES statement to verify that in the command line processor.

Tags for this Thread

Posting Permissions

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