Hello,
I have a table with a BLOB column which contains XML like the example below and I would like a SELECT statement to extract multiple rows with the <WbsElement> tag text value, IF IT IS PRESENT in the second section (<TbWbsText> section ).
That is, in the example below, I would like a “SELECT” statement that returns two rows:
CODE_00253
CODE_00085
Can you help me?
THANK YOU
Michele
================================================================================
XML:
================================================================================
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:ZcreazioneCommesse xmlns:ns2="urn:qqqiop-com:document:qqqiop:soap:functions:mc-style">
<TbWbsElement>
<item>
<WbsElement>CODE_00253</WbsElement>
<Description>Example 1</Description>
<Zsede>2</Zsede>
<ZareaApp>02</ZareaApp>
<Zunitprd>09</Zunitprd>
<Zdoce>a13833</Zdoce>
<Zaffdoce>aa41054</Zaffdoce>
<Zaffcom>aa41054</Zaffcom>
<Zentefin>aa231</Zentefin>
<Zclentefin>azPU</Zclentefin>
<Zstclentefin>azRG</Zstclentefin>
<Zsettsci>azAGR/03</Zsettsci>
<Zmodattr>aza1</Zmodattr>
<Zareasci>qqAREA07</Zareasci>
<Zlinea>rewE99</Zlinea>
<Zentegestore>229</Zentegestore>
<Usr06>3875.00</Usr06>
<Usr08>2021-04-28</Usr08>
<ZextProg>X</ZextProg>
</item>
<item>
<WbsElement>CODE_00085</WbsElement>
<Description>Example 2</Description>
<Zsede>1</Zsede>
<ZareaApp>02</ZareaApp>
<Zunitprd>07</Zunitprd>
<Zdoce>1re5007</Zdoce>
<Zaffdoce>3re0170</Zaffdoce>
<Zaffcom>cpoiRMsdasad-0480sad-0480</Zaffcom>
<Zentefin>aa41</Zentefin>
<Zclentefin>NC</Zclentefin>
<Zstclentefin>NC</Zstclentefin>
<Zsettsci>qqM-PSI/08</Zsettsci>
<Zmodattr>1</Zmodattr>
<Zareasci>4tAREA06</Zareasci>
<Zlinea>E99</Zlinea>
<Zentegestore>r27</Zentegestore>
<Usr06>39.50</Usr06>
<Usr08>2022-05-27</Usr08>
<ZextProg>X</ZextProg>
</item>
</TbWbsElement>
<TbWbsText>
<item>
<WbsElement>CODE_00253</WbsElement>
<Zlincount>1</Zlincount>
<Tdline>Text example #1 16.1.01 -SPQR 21-4-2020 - Text example #1 16.1.01 - F.A. 3A - D.G.R. n. 123153/2022</Tdline>
</item>
<item>
<WbsElement>CODE_00085</WbsElement>
<Zlincount>1</Zlincount>
<Tdline>Text example #2 16.1.01 -invoice #2345 2014-2020</Tdline>
</item>
</TbWbsText>
</ns2:ZcreazioneCommesse>
================================================================================