4 Replies Latest reply on Jul 30, 2019 11:17 AM by CarstenDD

    Default for varchar2 in XMLTABLE / COLUMNS

    CarstenDD

      Hello,

      I try to get a default value for a varchar2 column in XMLTABLE but my solution

       

      WITH testdata(xmlfile) AS (
      
      SELECT xmltype(
      '<FinancingContract>
          <Header>
              <Partner>
                  <ContractPartner>
                      <CompanyName>Endkunden Firma GmbH</CompanyName>
                      <CommunicationLanguage>de-DE</CommunicationLanguage>
                  </ContractPartner>
                  <Reseller>
                      <CompanyName>Resellername AG</CompanyName>
                  </Reseller>
              </Partner>
          </Header>
      </FinancingContract>'
      )
      FROM dual)
      
      SELECT x.*
      FROM testdata t,
            XMLTABLE ('
                       /FinancingContract/Header
                      '
                      PASSING t.xmlfile
                      COLUMNS CompanyName VARCHAR2(100) PATH 'Partner/ContractPartner/CompanyName'
                             ,CommunicationLanguage VARCHAR2(5) PATH 'Partner/ContractPartner/CommunicationLanguage'
                             ,CompanyName_Reseller VARCHAR2(100) PATH 'Partner/Reseller/CompanyName'
                             --,CommunicationLanguage_Res VARCHAR2(5) PATH 'Partner/Reseller/CommunicationLanguage'
                             ,CommunicationLanguage_Res VARCHAR2(5) DEFAULT 'de-DE' PATH 'Partner/Reseller/CommunicationLanguage'
                     ) x
      

       

      does not work.

      Any help is appreciated.

      Regards Carsten

        • 1. Re: Default for varchar2 in XMLTABLE / COLUMNS
          mNem

            ,CommunicationLanguage_Res VARCHAR2(5) PATH 'Partner/Reseller/CommunicationLanguage'   DEFAULT 'de-DE'

          • 2. Re: Default for varchar2 in XMLTABLE / COLUMNS
            Gareth S

            All you need to do in this case is change the position of the "DEFAULT" statement I think, so the following works for me:

            WITH testdata(xmlfile) AS (  
              
            SELECT xmltype(  
            '<FinancingContract>  
                <Header>  
                    <Partner>  
                        <ContractPartner>  
                            <CompanyName>Endkunden Firma GmbH</CompanyName>  
                            <CommunicationLanguage>de-DE</CommunicationLanguage>  
                        </ContractPartner>  
                        <Reseller>  
                            <CompanyName>Resellername AG</CompanyName>  
                        </Reseller>  
                    </Partner>  
                </Header>  
            </FinancingContract>'  
            )  
            FROM dual)  
              
            SELECT x.*  
            FROM testdata t,  
                  XMLTABLE ('  
                             /FinancingContract/Header  
                            '  
                            PASSING t.xmlfile  
                            COLUMNS CompanyName VARCHAR2(100) PATH 'Partner/ContractPartner/CompanyName'  
                                   ,CommunicationLanguage VARCHAR2(5) PATH 'Partner/ContractPartner/CommunicationLanguage'  
                                   ,CompanyName_Reseller VARCHAR2(100) PATH 'Partner/Reseller/CompanyName'  
                                   --,CommunicationLanguage_Res VARCHAR2(5) PATH 'Partner/Reseller/CommunicationLanguage'  
                                   ,CommunicationLanguage_Res VARCHAR2(5) PATH 'Partner/Reseller/CommunicationLanguage'  DEFAULT 'de-DE' 
                           ) x;
            

             

            For future reference, providing error messages etc rather than "it doesn't work" is a lot more helpful. For example when I first ran your statement I got:

            ORA-00907: missing right parenthesis
            00907. 00000 -  "missing right parenthesis"
            *Cause:    
            *Action:
            Error at Line: 30 Column: 79
            

             

            Which helps to narrow things down a bit.

             

            Best regards

             

            Gareth.

            • 3. Re: Default for varchar2 in XMLTABLE / COLUMNS
              CarstenDD

              Hello Gareth,

              thanks for the hint to provide error messages.I will do that next time.

               

              Regards

              Carsten

              • 4. Re: Default for varchar2 in XMLTABLE / COLUMNS
                CarstenDD

                Hello,

                 

                thanks a lot for the quick answer.

                 

                Regards

                Carsten