Parsing XML in DB using SQL. Getting display name as null also not able to get name="M_38517_KEY"

Question

 <ModelDefinition version="6.0">
              <ClientName>BIC</ClientName>
              <CountryCode>US</CountryCode>
              <DimensionName>ITEM</DimensionName>
              <JobLogId>1780752</JobLogId>
              <Specs>
                <Spec id="15495" name="BIC_18_MA">
                  <Model name="BIC_MARKET_ADVANTAGE" type="TSV" id="2237"/>
                </Spec>
              </Specs>
              <Dimension name="Product">
                <Folder name="MAUTO_18_CUSTOM_HIERARCHIES_MA_CSIA" ordernum="1"   visible="N" modelid="2237">
                  <Folder name="Lighters Hierarchies" ordernum="1" visible="Y">
                    <Hierarchy name="Lighters by Segment" type="Standard" ordernum="1">
                      <LevelGroups ordernum="1">
                        <LevelGroup name="CATEGORY" ordernum="1" visible="Y">
                          <DisplayName>CATEGORY_LTR_by_Segment</DisplayName>
                          <KeyColumns>
                            <Column name="M_38517_KEY"/>
                          </KeyColumns>
                         </LevelGroup>
                      </LevelGroups>
                    </Hierarchy>
               </Folder>
             </Folder>
            </Dimension>
            </ModelDefinition>

SELECT B.DISP_NAME FROM ITEM_XML_MODEL_AUTMN_TMP AD, XMLTABLE ('$d/ModelDefinition' PASSING XML_TX AS "d" COLUMNS ATTR_DET XMLTYPE PATH 'Dimension') A, XMLTABLE ('/Dimension' PASSING A.ATTR_DET COLUMNS DISP_NAME VARCHAR2(2000) PATH '/Folder/Folder/Hierarchy/LevelGroups/LevelGroup/DisplayName' ) B WHERE AD.JOB_LOG_ID = 1 AND AD.SPEC_ID = 111

Are you using MySQL or Oracle? Don't tag products not involved. — jarlh
– jarlh, Commented Sep 6, 2017 at 7:34

Arkadiusz Łukasiewicz · Accepted Answer · 2017-09-06 10:17:01Z

0

2-nd xmltable is wrong.

 xmltable('/Dimension'
                   passing A.ATTR_DET
                   columns DISP_NAME varchar2(2000) path '/Folder/Folder/Hierarchy/LevelGroups/LevelGroup/DisplayName') B

Should be (start after dimension)

 xmltable('/Dimension/*'
                   passing A.ATTR_DET
                   columns DISP_NAME varchar2(2000) path '/Folder/Folder/Hierarchy/LevelGroups/LevelGroup/DisplayName') B

or (without the slash in the path) For me, this option is better.

 xmltable('/Dimension'
                   passing A.ATTR_DET
                   columns DISP_NAME varchar2(2000) path 'Folder/Folder/Hierarchy/LevelGroups/LevelGroup/DisplayName') B

Update for expected singleton sequence - got multi-item sequence.

Probably the element LevelGroup exists more than one time in XML. Check this query

xmltable('/Dimension/Folder/Folder/Hierarchy/LevelGroups/LevelGroup'
                   passing A.ATTR_DET
                   columns DISP_NAME varchar2(2000) path 'DisplayName') B

edited Sep 6, 2017 at 10:17

answered Sep 6, 2017 at 8:14

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PKrishna Tamuli Over a year ago

If I go for '/Dimension/*' getting null for 2nd option "'/Dimension'" getting "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence 19279. 00000 - "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" *Cause: The XQuery sequence passed in had more than one item. *Action: Correct the XQuery expression to return a single item sequence.

Collectives™ on Stack Overflow

Parsing XML in DB using SQL. Getting display name as null also not able to get name="M_38517_KEY"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related