Forum Stats

  • 3,825,929 Users
  • 2,260,581 Discussions
  • 7,896,738 Comments

Discussions

Categories

building XPath with the XML having the Namespace using PL SQL

2»

Answers

  • Sandeep Sangameshwara-Oracle
    Sandeep Sangameshwara-Oracle Member Posts: 10

    Hi,

    Can you please provide an example for the query " what about unbounded sequence of elements, mapped to rows in your target table ?" asked.

    Thanks.

    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy

    For example, what will you do with this path :

    /a:SCTScfBlkCredTrf/b:FIToFICstmrCdtTrf

    You said in your first post that the FIToFICstmrCdtTrf element may occurs multiple times (= unbounded sequence in XSD terms).

    So far, I've been assuming that each occurrence of this element represents one row in your target table, is it correct?

    · Share on TwitterShare on Facebook
  • Sandeep Sangameshwara-Oracle
    Sandeep Sangameshwara-Oracle Member Posts: 10

    Hi,

    Yes, your understanding is correct.

    Our UI would show only the distinct records in the built XPath.

    Thanks.

    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy
    edited Nov 7, 2013 11:06AM

    So, for this particular path the UI must provide a way to mark it as a "row" identifier, right ?

    BTW, do you have the corresponding XML schemas at your disposal ?

    · Share on TwitterShare on Facebook
  • Sandeep Sangameshwara-Oracle
    Sandeep Sangameshwara-Oracle Member Posts: 10
    edited Nov 8, 2013 12:20AM

    Hi,

    Yes.

    Please find the request XML schema for.

    Since unable to find an option to attach the File, Copying the same in the editor.

    XSD : SCTCvfBlkCredTrf

    <?xml version="1.0" encoding="UTF-8"?>

    <!-- STEP2 SCT Schema, SCTCvfBlkCredTrf, March 23th 2012, Release November 2012  -->

    <!-- STEP2 Renamed pacs.002.001.03S2 to FIToFIPmtStsRptS2, March 29th 2010 -->

    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sw5="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03S2" xmlns:S2SCTCvf="urn:S2SCTCvf:xsd:$SCTCvfBlkCredTrf" xmlns:ns1="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03S2" targetNamespace="urn:S2SCTCvf:xsd:$SCTCvfBlkCredTrf" elementFormDefault="qualified">

        <xs:import namespace="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03S2" schemaLocation="pacs.002.001.03S2.xsd"/>

        <xs:element name="SCTCvfBlkCredTrf">

            <xs:complexType>

                <xs:sequence>

                    <xs:element name="SndgInst" type="sw5:BICIdentifier"/>

                    <xs:element name="RcvgInst" type="sw5:BICIdentifier"/>

                    <xs:element name="SrvcId" type="S2SCTCvf:SrvcID"/>

                    <xs:element name="TstCode" type="S2SCTCvf:TestCodeType"/>

                    <xs:element name="FType" type="S2SCTCvf:FTpType"/>

                    <xs:element name="FileRef" type="sw5:Max16Text"/>

                    <xs:element name="FileDtTm" type="sw5:ISODateTime"/>

                    <xs:element name="OrigFRef" type="sw5:Max16Text" minOccurs="0"/>

                    <xs:element name="OrigFName" type="S2SCTCvf:Max32Text"/>

                    <xs:element name="OrigDtTm" type="sw5:ISODateTime" minOccurs="0"/>

                    <xs:element name="FileRjctRsn" type="S2SCTCvf:Text3"/>

                    <xs:element name="FileBusDt" type="sw5:ISODate"/>

                    <xs:element name="FileCycleNo" type="S2SCTCvf:Max2NumericText"/>

                    <xs:element name="FIToFIPmtStsRptS2" type="sw5:FIToFIPaymentStatusReportV03" minOccurs="0" maxOccurs="1"/>

                </xs:sequence>

            </xs:complexType>

        </xs:element>

        <xs:simpleType name="Max32Text">

            <xs:restriction base="xs:string">

                <xs:minLength value="1"/>

                <xs:maxLength value="32"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="Text3">

            <xs:restriction base="xs:string">

                <xs:length value="3" fixed="true"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="FTpType">

            <xs:restriction base="xs:string">

                <xs:enumeration value="CVF"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="TestCodeType">

            <xs:restriction base="xs:string">

                <xs:length value="1"/>

                <xs:enumeration value="T"/>

                <xs:enumeration value="P"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="SrvcID">

            <xs:restriction base="xs:string">

                <xs:enumeration value="SCT"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="Max2NumericText">

            <xs:restriction base="xs:string">

                <xs:pattern value="[0-9]{2,2}"/>

            </xs:restriction>

        </xs:simpleType>

    </xs:schema>

    Dependant XSD : pacs.002.001.03S2

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>

    <!-- STEP2 SCT Schema, pacs.002.001.03S2, March 23th 2012, Release November 2012  -->

    <!-- STEP2 Renamed pacs.002.001.03S2 to FIToFIPmtStsRptS2, March 29th 2010 -->

    <xs:schema xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03S2" xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03S2" elementFormDefault="qualified">

        <xs:element name="Document" type="Document"/>

        <xs:simpleType name="S2SCTCurrencyAndAmount_SimpleType">

            <xs:restriction base="xs:decimal">

                <xs:minInclusive value="0"/>

                <xs:fractionDigits value="2"/>

                <xs:totalDigits value="18"/>

                <xs:pattern value="[0-9]{0,15}([\.]([0-9]{0,2})){0,1}"/>

                <!--xs:pattern added on 29-11-07-->

            </xs:restriction>

        </xs:simpleType>

        <xs:complexType name="S2SCTCurrencyAndAmount">

            <xs:simpleContent>

                <xs:extension base="S2SCTCurrencyAndAmount_SimpleType">

                    <xs:attribute name="Ccy" type="S2SCTCurrencyCode" use="required"/>

                </xs:extension>

            </xs:simpleContent>

        </xs:complexType>

        <xs:simpleType name="S2SCTCurrencyCode">

            <xs:restriction base="xs:string">

                <xs:enumeration value="EUR"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="AnyBICIdentifier">

            <xs:restriction base="xs:string">

                <xs:pattern value="[A-Z]{6,6}[A-Z2-9][A-NP-Z0-9]([A-Z0-9]{3,3}){0,1}"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="BICIdentifier">

            <xs:restriction base="xs:string">

                <xs:pattern value="[A-Z]{6,6}[A-Z2-9][A-NP-Z0-9]([A-Z0-9]{3,3}){0,1}"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:complexType name="S2SCTBranchAndFinancialInstitutionIdentification4">

            <xs:sequence>

                <xs:element name="FinInstnId" type="S2SCTFinancialInstitutionIdentification7"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="Document">

            <xs:sequence>

                <!-- STEP2 Renamed FIToFIPmtStsRptS2, March 29th 2010 -->

                <xs:element name="FIToFIPmtStsRptS2" type="FIToFIPaymentStatusReportV03"/>

            </xs:sequence>

        </xs:complexType>

        <!--new type S2SCTExternalStatusReason1Code since 05-06-07-->

        <xs:simpleType name="S2SCTExternalStatusReason1Code">

            <xs:restriction base="xs:string">

                <xs:enumeration value="ED05"/>

            </xs:restriction>

        </xs:simpleType>

        <!--new type S2SCTExternalStatusReason2Code since 05-06-07-->

        <xs:simpleType name="S2SCTExternalStatusReason2Code">

            <xs:restriction base="xs:string">

                <xs:enumeration value="AM01"/>

                <xs:enumeration value="AM02"/>

                <xs:enumeration value="AM05"/>

                <xs:enumeration value="DT01"/>

                <xs:enumeration value="ED05"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="S2SCTTransactionIndividualStatus1Code">

            <xs:restriction base="xs:string">

                <xs:enumeration value="ACTC"/>

                <xs:enumeration value="RJCT"/>

                <xs:enumeration value="PDNG"/>

                <xs:enumeration value="ACCP"/>

                <xs:enumeration value="ACSP"/>

                <xs:enumeration value="ACSC"/>

                <xs:enumeration value="ACCR"/>

                <xs:enumeration value="ACWC"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:complexType name="FIToFIPaymentStatusReportV03">

            <xs:sequence>

                <xs:element name="GrpHdr" type="S2SCTGroupHeader37"/>

                <xs:element name="OrgnlGrpInfAndSts" type="OriginalGroupInformation20"/>

                <xs:element name="TxInfAndSts" type="PaymentTransactionInformation26" minOccurs="0" maxOccurs="unbounded"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTFinancialInstitutionIdentification7">

            <xs:sequence>

                <xs:element name="BIC" type="BICIdentifier"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTGroupHeader37">

            <xs:sequence>

                <xs:element name="MsgId" type="S2SCTId7"/>

                <xs:element name="CreDtTm" type="ISODateTime"/>

                <xs:element name="InstgAgt" type="S2SCTBranchAndFinancialInstitutionIdentification4" minOccurs="0"/>

            </xs:sequence>

        </xs:complexType>

        <xs:simpleType name="ISODate">

            <xs:restriction base="xs:date"/>

        </xs:simpleType>

        <xs:simpleType name="ISODateTime">

            <xs:restriction base="xs:dateTime"/>

        </xs:simpleType>

        <xs:simpleType name="Max15NumericText">

            <xs:restriction base="xs:string">

                <xs:pattern value="[0-9]{1,15}"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="Max16Text">

            <xs:restriction base="xs:string">

                <xs:minLength value="1"/>

                <xs:maxLength value="16"/>

                <xs:whiteSpace value="collapse"/>

                <xs:pattern value="\S+.*"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="Max35Text">

            <xs:restriction base="xs:string">

                <xs:minLength value="1"/>

                <xs:maxLength value="35"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:complexType name="NumberOfTransactionsPerStatus3">

            <xs:sequence>

                <xs:element name="DtldNbOfTxs" type="Max15NumericText"/>

                <xs:element name="DtldSts" type="S2SCTTransactionIndividualStatus1Code"/>

                <xs:element name="DtldCtrlSum" type="S2SCTCurrencyAndAmount_SimpleType"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="OriginalGroupInformation20">

            <xs:sequence>

                <xs:element name="OrgnlMsgId" type="Max35Text"/>

                <xs:element name="OrgnlMsgNmId" type="Max35Text"/>

                <xs:element name="OrgnlNbOfTxs" type="Max15NumericText"/>

                <xs:element name="OrgnlCtrlSum" type="S2SCTCurrencyAndAmount_SimpleType"/>

                <xs:element name="GrpSts" type="S2SCTTransactionGroupStatus3Code"/>

                <xs:element name="StsRsnInf" type="S2SCTStatusReasonInformation8"/>

                <xs:element name="NbOfTxsPerSts" type="NumberOfTransactionsPerStatus3" minOccurs="0" maxOccurs="2"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="OriginalTransactionReference13">

            <xs:sequence>

                <xs:element name="IntrBkSttlmAmt" type="S2SCTCurrencyAndAmount"/>

                <xs:element name="IntrBkSttlmDt" type="ISODate"/>

                <xs:element name="DbtrAgt" type="S2SCTBranchAndFinancialInstitutionIdentification4"/>

                <xs:element name="CdtrAgt" type="S2SCTBranchAndFinancialInstitutionIdentification4"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="PaymentTransactionInformation26">

            <xs:sequence>

                <xs:element name="StsId" type="S2SCTId7"/>

                <xs:element name="OrgnlInstrId" type="S2SCTId7" minOccurs="0"/>

                <xs:element name="OrgnlEndToEndId" type="Max35Text"/>

                <xs:element name="OrgnlTxId" type="S2SCTId7"/>

                <xs:element name="TxSts" type="TransactionIndividualStatus3Code"/>

                <xs:element name="StsRsnInf" type="S2SCTStatusReasonInformation9"/>

                <xs:element name="InstdAgt" type="S2SCTBranchAndFinancialInstitutionIdentification4" minOccurs="0"/>

                <xs:element name="OrgnlTxRef" type="OriginalTransactionReference13" minOccurs="0"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="StatusReason6Choice">

            <xs:sequence>

                <xs:choice>

                    <xs:element name="Cd" type="S2SCTExternalStatusReason1Code"/>

                    <xs:element name="Prtry" type="Max35Text"/>

                </xs:choice>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="StatusReason7Choice">

            <xs:sequence>

                <xs:choice>

                    <xs:element name="Cd" type="S2SCTExternalStatusReason2Code"/>

                    <xs:element name="Prtry" type="Max35Text"/>

                </xs:choice>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTOrganisationIdentification4">

            <xs:sequence>

                <xs:element name="BICOrBEI" type="AnyBICIdentifier"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTParty3Choice">

            <xs:sequence>

                <xs:element name="OrgId" type="S2SCTOrganisationIdentification4"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTId5">

            <xs:sequence>

                <xs:element name="Id" type="S2SCTParty3Choice"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTStatusReasonInformation8">

            <xs:sequence>

                <xs:element name="Orgtr" type="S2SCTId5"/>

                <xs:element name="Rsn" type="StatusReason6Choice"/>

            </xs:sequence>

        </xs:complexType>

        <xs:complexType name="S2SCTStatusReasonInformation9">

            <xs:sequence>

                <xs:element name="Orgtr" type="S2SCTId5"/>

                <xs:element name="Rsn" type="StatusReason7Choice"/>

            </xs:sequence>

        </xs:complexType>

        <xs:simpleType name="S2SCTTransactionGroupStatus3Code">

            <xs:restriction base="xs:string">

                <xs:enumeration value="ACCP"/>

                <xs:enumeration value="PART"/>

                <xs:enumeration value="RJCT"/>

            </xs:restriction>

        </xs:simpleType>

        <!-- Value PDNG added due to CR 3352 - 2011-04-01 -->

        <xs:simpleType name="TransactionIndividualStatus3Code">

            <xs:restriction base="xs:string">

                <xs:enumeration value="RJCT"/>

                <xs:enumeration value="PDNG"/>

            </xs:restriction>

        </xs:simpleType>

        <xs:simpleType name="S2SCTId7">

            <xs:restriction base="xs:string">

                <xs:pattern value="([A-Za-z0-9]|[+|\?|/|\-|:|\(|\)|\.|,|']){1,35}"/>

                <!--TxId and MsgId extended to support full character "+" on 05-06-07-->

                <!--TxId and MsgId extended to support full Latin character set on 08-03-07-->

                <!--xs:pattern value="([A-Za-z0-9]|[\?|/|\-|:|\(|\)|\.|,|']){1,35}"/-->

            </xs:restriction>

        </xs:simpleType>

    </xs:schema>

    Thanks.

    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy
    edited Nov 11, 2013 11:18AM Answer ✓

    Thanks.

    If you have the schemas, then it may be easier to work on them directly.

    Here's the idea :

    1) Install "Oracle XML DB Manageability Packages", available on XML DB sample code page : http://download.oracle.com/otn/samplecode/xdb_util.zip

    It's a set of utilities that help us annotate XML schemas, deal with the underlying OR storage structure, and (this is the interesting part here) expose a few dictionary views to describe XSD structure relationally.

    (it's now a 12c built-in btw)

    2) Register the two schemas :

    begin
  dbms_xmlschema.registerSchema(
    schemaURL       => 'pacs.002.001.03S2.xsd'
  , schemaDoc       => xmltype(bfilename('TEST_DIR','pacs.002.001.03S2.xsd'), nls_charset_id('AL32UTF8'))
  , local           => true
  , genTypes        => false
  , genTables       => false
  , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
  , options         => dbms_xmlschema.REGISTER_BINARYXML
  );
end;
/

begin
  dbms_xmlschema.registerSchema(
    schemaURL       => 'SCTCvfBlkCredTrf.xsd'
  , schemaDoc       => xmltype(bfilename('TEST_DIR','SCTCvfBlkCredTrf.xsd'), nls_charset_id('AL32UTF8'))
  , local           => true
  , genTypes        => false
  , genTables       => false
  , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
  , options         => dbms_xmlschema.REGISTER_BINARYXML
  );
end;
/

    3) The following query builds all node paths defined by the main schema (SCTCvfBlkCredTrf.xsd), along with the namespaces :

    with schema_list (schema_url, schema_owner) as (
    select 'SCTCvfBlkCredTrf.xsd', 'OTN' from dual
    union all
    select sd.dep_schema_url, sd.dep_schema_owner
    from schema_list sl
         join dba_xml_schema_dependency sd on sd.schema_url = sl.schema_url
                                          and sd.schema_owner = sl.schema_owner
),
namespace_mapping (target_namespace, prefix) as (
    select target_namespace
         , 'ns' || row_number() over(order by target_namespace) as prefix
    from user_xml_schema_namespaces
    where schema_url in ( select schema_url from schema_list )
),
schema_nodes (
    is_attr
,   node_name
,   max_occurs
,   element_id
,   parent_element_id
,   target_namespace
,   schema_url
) as ( 
    select 0
         , element_name as node_name
         , xmlcast(
             xmlquery('/xs:element/@maxOccurs' passing element returning content) 
             as varchar2(10)
           )
         , element_id
         , parent_element_id
         , target_namespace
         , schema_url
    from user_xml_schema_elements
    where schema_url in ( select schema_url from schema_list )
    union all
    select 1
         , attribute_name as node_name
         , null
         , null
         , element_id
         , target_namespace
         , schema_url
    from user_xml_schema_attributes
    where schema_url in ( select schema_url from schema_list )
)
select level
     , sn.node_name
     , sn.max_occurs
     , connect_by_isleaf as is_leaf
     , sys_connect_by_path(
         case when ns.prefix is not null and is_attr = 0 then ns.prefix || ':' end ||
         case when sn.is_attr = 1 then '@' end ||          
         sn.node_name
       , '/'
       ) as xpath
     --, target_namespace
from schema_nodes sn
     left outer join namespace_mapping ns on ns.target_namespace = sn.target_namespace
connect by prior sn.element_id = sn.parent_element_id
start with sn.schema_url = 'SCTCvfBlkCredTrf.xsd'
       and sn.parent_element_id is null
;

    

LEVEL NODE_NAME          MAX_OCCURS IS_LEAF XPATH
    ----- ------------------ ---------- ------- ----------------------------------------------------------------------------------------------------------
        1 SCTCvfBlkCredTrf                    0 /ns2:SCTCvfBlkCredTrf
        2 SndgInst                            1 /ns2:SCTCvfBlkCredTrf/ns2:SndgInst
        2 RcvgInst                            1 /ns2:SCTCvfBlkCredTrf/ns2:RcvgInst
        2 SrvcId                              1 /ns2:SCTCvfBlkCredTrf/ns2:SrvcId
        2 TstCode                             1 /ns2:SCTCvfBlkCredTrf/ns2:TstCode
        2 FType                               1 /ns2:SCTCvfBlkCredTrf/ns2:FType
        2 FileRef                             1 /ns2:SCTCvfBlkCredTrf/ns2:FileRef
        2 FileDtTm                            1 /ns2:SCTCvfBlkCredTrf/ns2:FileDtTm
        2 OrigFRef                            1 /ns2:SCTCvfBlkCredTrf/ns2:OrigFRef
        2 OrigFName                           1 /ns2:SCTCvfBlkCredTrf/ns2:OrigFName
        2 OrigDtTm                            1 /ns2:SCTCvfBlkCredTrf/ns2:OrigDtTm
        2 FileRjctRsn                         1 /ns2:SCTCvfBlkCredTrf/ns2:FileRjctRsn
        2 FileBusDt                           1 /ns2:SCTCvfBlkCredTrf/ns2:FileBusDt
        2 FileCycleNo                         1 /ns2:SCTCvfBlkCredTrf/ns2:FileCycleNo
        2 FIToFIPmtStsRptS2  1                0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2
        3 GrpHdr                              0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr
        4 MsgId                               1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr/ns1:MsgId
        4 CreDtTm                             1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr/ns1:CreDtTm
        4 InstgAgt                            0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr/ns1:InstgAgt
        5 FinInstnId                          0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr/ns1:InstgAgt/ns1:FinInstnId
        6 BIC                                 1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:GrpHdr/ns1:InstgAgt/ns1:FinInstnId/ns1:BIC
        3 OrgnlGrpInfAndSts                   0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts
        4 OrgnlMsgId                          1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:OrgnlMsgId
        4 OrgnlMsgNmId                        1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:OrgnlMsgNmId
        4 OrgnlNbOfTxs                        1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:OrgnlNbOfTxs
        4 OrgnlCtrlSum                        1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:OrgnlCtrlSum
        4 GrpSts                              1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:GrpSts
        4 StsRsnInf                           0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf
        5 Orgtr                               0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Orgtr
        6 Id                                  0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id
        7 OrgId                               0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id/ns1:OrgId
        8 BICOrBEI                            1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id/ns1:OrgId/ns1:BICOrBEI
        5 Rsn                                 0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Rsn
        6 Cd                                  1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Rsn/ns1:Cd
        6 Prtry                               1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:StsRsnInf/ns1:Rsn/ns1:Prtry
        4 NbOfTxsPerSts      2                0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:NbOfTxsPerSts
        5 DtldNbOfTxs                         1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:NbOfTxsPerSts/ns1:DtldNbOfTxs
        5 DtldSts                             1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:NbOfTxsPerSts/ns1:DtldSts
        5 DtldCtrlSum                         1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:OrgnlGrpInfAndSts/ns1:NbOfTxsPerSts/ns1:DtldCtrlSum
        3 TxInfAndSts        unbounded        0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts
        4 StsId                               1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsId
        4 OrgnlInstrId                        1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlInstrId
        4 OrgnlEndToEndId                     1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlEndToEndId
        4 OrgnlTxId                           1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxId
        4 TxSts                               1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:TxSts
        4 StsRsnInf                           0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf
        5 Orgtr                               0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Orgtr
        6 Id                                  0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id
        7 OrgId                               0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id/ns1:OrgId
        8 BICOrBEI                            1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Orgtr/ns1:Id/ns1:OrgId/ns1:BICOrBEI
        5 Rsn                                 0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Rsn
        6 Cd                                  1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Rsn/ns1:Cd
        6 Prtry                               1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:StsRsnInf/ns1:Rsn/ns1:Prtry
        4 InstdAgt                            0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:InstdAgt
        5 FinInstnId                          0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:InstdAgt/ns1:FinInstnId
        6 BIC                                 1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:InstdAgt/ns1:FinInstnId/ns1:BIC
        4 OrgnlTxRef                          0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef
        5 IntrBkSttlmAmt                      0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:IntrBkSttlmAmt
        6 Ccy                                 1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:IntrBkSttlmAmt/@Ccy
        5 IntrBkSttlmDt                       1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:IntrBkSttlmDt
        5 DbtrAgt                             0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:DbtrAgt
        6 FinInstnId                          0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:DbtrAgt/ns1:FinInstnId
        7 BIC                                 1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:DbtrAgt/ns1:FinInstnId/ns1:BIC
        5 CdtrAgt                             0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:CdtrAgt
        6 FinInstnId                          0 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:CdtrAgt/ns1:FinInstnId
        7 BIC                                 1 /ns2:SCTCvfBlkCredTrf/ns2:FIToFIPmtStsRptS2/ns1:TxInfAndSts/ns1:OrgnlTxRef/ns1:CdtrAgt/ns1:FinInstnId/ns1:BIC

    Actually, you would typically extract the namespace mapping data in a separate query so that you can use it to build the dynamic XMLNamespaces clause.

    NB1 : Also note that not all paths are needed, only those whose IS_LEAF = 1 or MAX_OCCURS = 'unbounded' or MAX_OCCURS > '1', so you may want to add a filter predicate in the query directly.

    NB2 : The query doesn't handle every feature of XSD, for example I've relied on the default behaviour for the 'element_form_default' and 'attribute_form_default' attributes.

    BluShadow
    · Share on TwitterShare on Facebook
This discussion has been closed.