select
  "TEMPLATEID",
  "type",
  "language",
  "category",
  "initiatorID",
  "agency",
  "agencycontact",
  "anzeigencode",
  "ourOrderNumber",
  "yourOrderNumber",
  "costcenter",
  "PREVIEWRESOLUTION",
  "dontShowPreview",
  "useraccess",
  "campaign",
  "family",
  "model",
  "shortdescription",
  "longdescription",
  "COLUMNS",
  "WIDTH",
  "HEIGHT",
  "COLORS",
  "spotcolor",
  "womaColor",
  "womaType",
  "BLEED",
  "BORDER",
  "DOTGAIN",
  "ART",
  "MINDEALERS",
  "MAXDEALERS",
  "modulWortmarkeSwitch",
  "visiblefrom",
  "visibleto",
  "VALIDFROM",
  "VALIDTO",
  "MVRELEASEDATE",
  "MVRELEASEUSER",
  "RELEASEDATE",
  "RELEASEUSER",
  "WITHDRAWNDATE",
  "WITHDRAWNUSER",
  "AUTHOR",
  "CREATIONDATE",
  "serviceonly",
  "nlaccess",
  "finanzierungsart"
from
(
  select
    '1'                                               as "SORT",
    'id'                                              as "TEMPLATEID",
    'byPath(/root/documentinfo/type)'                 as "type",
    'byPath(/root/documentinfo/language)'             as "language",
    'byPath(/root/documentinfo/category)'             as "category",
    'byPath(/root/documentinfo/initiatorID)'          as "initiatorID",
    'byPath(/root/documentinfo/agency)'               as "agency",
    'byPath(/root/documentinfo/agencycontact)'        as "agencycontact",
    'byPath(/root/documentinfo/anzeigencode)'         as "anzeigencode",
    'byPath(/root/documentinfo/ourOrderNumber)'       as "ourOrderNumber",
    'byPath(/root/documentinfo/yourOrderNumber)'      as "yourOrderNumber",
    'byPath(/root/documentinfo/costcenter)'           as "costcenter",
    'byPath(/root/documentinfo/preview-resolution)'   as
"PREVIEWRESOLUTION",
    'byPath(/root/documentinfo/dontShowPreview)'      as "dontShowPreview",
    'byPath(/root/documentinfo/useraccess)'           as "useraccess",
    'byPath(/root/documentinfo/campaign)'             as "campaign",
    'byPath(/root/documentinfo/family)'               as "family",
    'byPath(/root/documentinfo/model)'                as "model",
    'byPath(/root/documentinfo/shortdescription)'     as "shortdescription",
    'byPath(/root/documentinfo/longdescription)'      as "longdescription",
    'byPath(/root/documentinfo/columns)'              as "COLUMNS",
    'byPath(/root/documentinfo/width)'                as "WIDTH",
    'byPath(/root/documentinfo/height)'               as "HEIGHT",
    'byPath(/root/documentinfo/colors)'               as "COLORS",
    'byPath(/root/documentinfo/spotcolor)'            as "spotcolor",
    'byPath(/root/documentinfo/womaColor)'            as "womaColor",
    'byPath(/root/documentinfo/womaType)'             as "womaType",
    'byPath(/root/documentinfo/bleed)'                as "BLEED",
    'byPath(/root/documentinfo/border)'               as "BORDER",
    'byPath(/root/documentinfo/dotgain)'              as "DOTGAIN",
    'byPath(/root/documentinfo/art)'                  as "ART",
    'byPath(/root/documentinfo/mindealers)'           as "MINDEALERS",
    'byPath(/root/documentinfo/maxdealers)'           as "MAXDEALERS",
    'byPath(/root/documentinfo/modulWortmarkeSwitch)' as
"modulWortmarkeSwitch",
    'byPath(/root/documentinfo/visiblefrom)'          as "visiblefrom",
    'byPath(/root/documentinfo/visibleto)'            as "visibleto",
    'byPath(/root/documentinfo/validfrom)'            as "VALIDFROM",
    'byPath(/root/documentinfo/validto)'              as "VALIDTO",
    'byPath(/root/documentinfo/mvreleasedate)'        as "MVRELEASEDATE",
    'byPath(/root/documentinfo/mvreleaseuser)'        as "MVRELEASEUSER",
    'byPath(/root/documentinfo/releasedate)'          as "RELEASEDATE",
    'byPath(/root/documentinfo/releaseuser)'          as "RELEASEUSER",
    'byPath(/root/documentinfo/withdrawdate)'         as "WITHDRAWNDATE",
    'byPath(/root/documentinfo/withdrawuser)'         as "WITHDRAWNUSER",
    'byPath(/root/documentinfo/author)'               as "AUTHOR",
    'byPath(/root/documentinfo/creationdate)'         as "CREATIONDATE",
    'byPath(/root/documentinfo/serviceonly)'          as "serviceonly",
    'byPath(/root/documentinfo/nlaccess)'             as "nlaccess",
    'byPath(/root/documentinfo/finanzierungsart)'     as "finanzierungsart"
 
  from
    dual
 
  union
 
  select 
    '2'                                               as "SORT",
    `id`                                              as "TEMPLATEID",
    `doctype`                                         as "type",
    `language`                                        as "language",
    `category`                                        as "category",
    `initiatorID`                                     as "initiatorID",
    substr(xmltempl, instr(xmltempl, '<agency>')       +length('agency')
+2, instr(xmltempl, '</agency>')       -instr(xmltempl, '<agency>')
-length('agency')       -2) as "agency",
    substr(xmltempl, instr(xmltempl,
'<agencycontact>')+length('agencycontact')+2, instr(xmltempl,
'</agencycontact>')-instr(xmltempl,
'<agencycontact>')-length('agencycontact')-2) as "agencycontact",
    `anzeigencode`                                    as "anzeigencode",
    `ourOrderNumber`                                  as "ourOrderNumber",
    substr(xmltempl, instr(xmltempl,
'<yourOrderNumber>')+length('yourOrderNumber')+2, instr(xmltempl,
'</yourOrderNumber>')-instr(xmltempl,
'<yourOrderNumber>')-length('yourOrderNumber')-2) as "yourOrderNumber",
    substr(xmltempl, instr(xmltempl, '<costcenter>')+length('costcenter')+2,
instr(xmltempl, '</costcenter>')-instr(xmltempl,
'<costcenter>')-length('costcenter')-2) as "costcenter",
    substr(xmltempl, instr(xmltempl,
'<preview-resolution>')+length('preview-resolution')+2, instr(xmltempl,
'</preview-resolution>')-instr(xmltempl,
'<preview-resolution>')-length('preview-resolution')-2) as
"PREVIEWRESOLUTION",
    substr(xmltempl, instr(xmltempl,
'<dontShowPreview>')+length('dontShowPreview')+2, instr(xmltempl,
'</dontShowPreview>')-instr(xmltempl,
'<dontShowPreview>')-length('dontShowPreview')-2) as "dontShowPreview",
    substr(xmltempl, instr(xmltempl, '<useraccess>')+length('useraccess')+2,
instr(xmltempl, '</useraccess>')-instr(xmltempl,
'<useraccess>')-length('useraccess')-2) as "useraccess",
    `campaign`                                        as "campaign",
    `family`                                          as "family",
    substr(xmltempl, instr(xmltempl, '<model>')+length('model')+2,
instr(xmltempl, '</model>')-instr(xmltempl, '<model>')-length('model')-2) as
"model",
    `shortdescription`                                as "shortdescription",
    `longdescription`                                 as "longdescription",
    `columns`                                         as "COLUMNS",
    width                                             as "WIDTH",
    height                                            as "HEIGHT",
    colors                                            as "COLORS",
    spotcolor                                         as "spotcolor",
    substr(xmltempl, instr(xmltempl, '<womaColor>')+length('womaColor')+2,
instr(xmltempl, '</womaColor>')-instr(xmltempl,
'<womaColor>')-length('womaColor')-2) as "womaColor",
    substr(xmltempl, instr(xmltempl, '<womaType>')+length('womaType')+2,
instr(xmltempl, '</womaType>')-instr(xmltempl,
'<womaType>')-length('womaType')-2) as "womaType",
    substr(xmltempl, instr(xmltempl, '<bleed>')+length('bleed')+2,
instr(xmltempl, '</bleed>')-instr(xmltempl, '<bleed>')-length('bleed')-2) as
"BLEED",
    substr(xmltempl, instr(xmltempl, '<border>')+length('border')+2,
instr(xmltempl, '</border>')-instr(xmltempl, '<border>')-length('border')-2)
as "BORDER",
    dotgain                                           as "DOTGAIN",
    art                                               as "ART",
    mindealers                                        as "MINDEALERS",
    maxdealers                                        as "MAXDEALERS",
    substr(xmltempl, instr(xmltempl,
'<modulWortmarkeSwitch>')+length('modulWortmarkeSwitch')+2, instr(xmltempl,
'</modulWortmarkeSwitch>')-instr(xmltempl,
'<modulWortmarkeSwitch>')-length('modulWortmarkeSwitch')-2) as
"modulWortmarkeSwitch",
    substr(xmltempl, instr(xmltempl,
'<visiblefrom>')+length('visiblefrom')+2, instr(xmltempl,
'</visiblefrom>')-instr(xmltempl, '<visiblefrom>')-length('visiblefrom')-2)
as "visiblefrom",
    substr(xmltempl, instr(xmltempl, '<visibleto>')+length('visibleto')+2,
instr(xmltempl, '</visibleto>')-instr(xmltempl,
'<visibleto>')-length('visibleto')-2) as "visibleto",
    validfrom                                         as "VALIDFROM",
    validto                                           as "VALIDTO",
    mvreleasedate                                     as "MVRELEASEDATE",
    mvreleaseuser                                     as "MVRELEASEUSER",
    releasedate                                       as "RELEASEDATE",
    releaseuser                                       as "RELEASEUSER",
    withdrawndate                                     as "WITHDRAWNDATE",
    withdrawnuser                                     as "WITHDRAWNUSER",
    author                                            as "AUTHOR",
    creationdate                                      as "CREATIONDATE",
    substr(xmltempl, instr(xmltempl,
'<serviceonly>')+length('serviceonly')+2, instr(xmltempl,
'</serviceonly>')-instr(xmltempl, '<serviceonly>')-length('serviceonly')-2)
as "serviceonly",
    substr(xmltempl, instr(xmltempl, '<nlaccess>')+length('nlaccess')+2,
instr(xmltempl, '</nlaccess>')-instr(xmltempl,
'<nlaccess>')-length('nlaccess')-2) as "nlaccess",
    substr(xmltempl, instr(xmltempl,
'<finanzierungsart>')+length('finanzierungsart')+2, instr(xmltempl,
'</finanzierungsart>')-instr(xmltempl,
'<finanzierungsart>')-length('finanzierungsart')-2) as "finanzierungsart"
  from
    templatedata
  where
    activeto is null and
    campaign like 'Aftersales DVD Systeme 2005/09'
) x

order by
  SORT,
  CAMPAIGN,
  FAMILY,
  TEMPLATEID