Extracting XML Data with SQL -

Extracting XML Data with SQL -

i have next xml info stored in sql table

<customfields xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns="http://www.kaseya.com/vsa/2007/12/servicedeskdefinition.xsd"> <field fieldname="changerequest">no</field> <field fieldname="problemrecord">no</field> <field fieldname="source">email</field> <field fieldname="kb_article">no</field> <field fieldname="optimusref">264692</field> <field fieldname="timespentonticket">0.25</field> <field fieldname="ponumber" /> <field fieldname="resourceassignedengineer" />

what select timespentonticket value form stored procedure.

any ideas how can this?

the problem here xml. it's invalid, there's not way search until prepare it. simple way check using online tool one @ w3schools. issue see namespace (xmlns) reference no longer exists. think mess postgres well, i'm not 100% on that. might have filter out when ingesting. however, after fixing xml, it's pretty easy things out using xpath within appropriate xml function.

for example, using next table:

create table bla.temptable (id int, myxml xml)

then, insert valid version of xml:

insert bla.temptable ( id, myxml ) select 1 id, '<?xml version="1.0" encoding="utf-8"?> <customfields> <field fieldname="changerequest">no</field> <field fieldname="problemrecord">no</field> <field fieldname="source">email</field> <field fieldname="kb_article">no</field> <field fieldname="optimusref">264692</field> <field fieldname="timespentonticket">0.25</field> <field fieldname="ponumber" /> <field fieldname="resourceassignedengineer" /> </customfields>' myxml

then, query out, can next (you can test xpath tool this, if need to):

select tt.id, tt.myxml, xpath('/customfields//field[@fieldname=''timespentonticket'']/text()', tt.myxml) bla.temptable tt

sql xml stored-procedures