CODEWITHSUNDEEP
sqloracle-databasexpathxqueryxmltable
ArpanMona
ArpanMona

Reputation: 59

SELECT query on xmltable

I'm trying to perform a select query as follows, where I'm Passing a small xml as xmltype.

SELECT x.PO_STEP,x.INSTL_ID,X.INSTL_LOC_CODE from xmltable
(
  XMLNAMESPACES('namespace1' as "v20", 'namespace2' as "v201"),
'/v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails/*[text()]'
PASSING xmltype(' <v20:ProductionOrder xmlns="***something***" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v20="namespace1">

        <v201:ProductionOrderSteps xmlns:v201="namespace2">
          <v201:ProductionOrderStep>
            <v201:POStepDetails>
              <v201:PO_STEP>1L</v201:PO_STEP>
              <v201:InstallationDetails>
                <v201:INSTL_ID>032</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE>N</v201:INSTL_LOC_CODE>
              </v201:InstallationDetails>
              <v201:InstallationDetails>
                <v201:INSTL_ID>000</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE />
              </v201:InstallationDetails>
              <v201:InstallationDetails>
                <v201:INSTL_ID>000</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE />
              </v201:InstallationDetails>
              <v201:InstallationDetails>
                <v201:INSTL_ID>000</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE />
              </v201:InstallationDetails>
            </v201:POStepDetails>
          </v201:ProductionOrderStep>
          <v201:ProductionOrderStep>
            <v201:POStepDetails>
              <v201:PO_STEP>1B</v201:PO_STEP>
              <v201:InstallationDetails>
                <v201:INSTL_ID>001</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE>N</v201:INSTL_LOC_CODE>
              </v201:InstallationDetails>
              <v201:InstallationDetails>
                <v201:INSTL_ID>000</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE />
              </v201:InstallationDetails>
              <v201:InstallationDetails>
                <v201:INSTL_ID>000</v201:INSTL_ID>
                <v201:INSTL_LOC_CODE />
              </v201:InstallationDetails>
            </v201:POStepDetails>
          </v201:ProductionOrderStep>
        </v201:ProductionOrderSteps>
      </v20:ProductionOrder>')

 COLUMNS
 PO_STEP VARCHAR2(20) PATH './parent::*/parent::*/v201:PO_STEP',
 INSTL_ID VARCHAR2(30) PATH '//v201:INSTL_ID',
 INSTL_LOC_CODE VARCHAR2(30) PATH '//v201:INSTL_LOC_CODE'
)x

but I am getting the following result

    PO_STEP     INSTL_ID  INSTL_LOC_CODE
        1L      null        null
        1L      null        null
        1L      null        null
        1L      null        null
        1L      null        null
        1B      null        null
        1B      null        null
        1B      null        null
        1B      null        null

I am not able to understand what is wrong with the Xpath, whether the xpath is being passed wrongly alongside the columns or the xpath written on top is wrong. I am using oracle sql developer

Upvotes: 0

Views: 639

Answers (1)

SomeDude
SomeDude

Reputation: 14238

Change the xpath

/v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails/*[text()]

to

/v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails

To get the first occurrence add [1] in the end like :

/v20:ProductionOrder/v201:ProductionOrderSteps/v201:ProductionOrderStep/v201:POStepDetails/v201:InstallationDetails[1]

AND

Change

./parent::*/parent::*/v201:PO_STEP

to

./parent::*/v201:PO_STEP

The result will be :

1L  032 N
1L  000 
1L  000 
1L  000 
1B  001 N
1B  000 
1B  000

For the first occurrence only, the result will be :

1L 032 N
1B 001 N

Upvotes: 0

Related Questions