Values from xml data field in mysql-CodePudding

i would like to know if there is a query to select values from all of my xml data fields. There are around 1k rows which has xml data. All of them has almost the same data structure. With extract value i was able to extract one data field but at the point where more than one row is part of my subquery it breaks.

Here is an example xml data inside my db:

    <EDLXML version="1.0.0" type="variable">
  <properties id="template_variables">
    <deliveredDuration>4444</deliveredDuration>
    <deliveredNum>1</deliveredNum>
    <comment/>
    <projectname>cdfkusen</projectname>
    <name>kral_schalke_trenink</name>
    <order_id>372846</order_id>
    <cutlistId>2763_ID</cutlistId>
    <bcutlistId>51ddgf7a6-1268-1gdfged-95e6-5254000e8e1a</bcutlistId>
    <num>1</num>
    <duration>177760</duration>
    <quotaRelevantDuration>0</quotaRelevantDuration>
    <organisationUid>OrgName</organisationUid>
    <organisationQuota>333221233</organisationQuota>
    <organisationUsedQuota>123</organisationUsedQuota>
    <organisationContingentIrrelevantQuotaUsed>54</organisationContingentIrrelevantQuotaUsed>
    <userDbId>7xxxx84-eb9b-11fdsb-9ddd1-52cccccde1a</userDbId>
    <userId>xxxx</userId>
    <userRights>RH_DBC</userRights>
    <firstName>DThom</firstName>
    <lastName>Test</lastName>
    <userMail>[email protected]</userMail>
    <language>English</language>
    <orderTimestamp>1659448080</orderTimestamp>
    <stitching>false</stitching>
    <transcode>NO</transcode>
    <destination>Standard</destination>
    <collaboration>private</collaboration>
    <premiumUser>false</premiumUser>
    <priority>normal</priority>
    <userMail2>[email protected]</userMail2>
    <cutlistItems>
      <cutListId>125124_KFC</cutListId>
      <cutListItemId cutlistItemDeliveryStatus="&amp;#10004" cutlistItemDStatusMessage="delivered">112799</cutListItemId>
      <bmarkerId>8f16ff80-1269-11ed-95e6-5254000e8e1a</bmarkerId>
      <videoId>2912799</videoId>
      <counter>1</counter>
      <frameInSpecified>true</frameInSpecified>
      <frameIn>15638</frameIn>
      <frameOutSpecified>true</frameOutSpecified>
      <frameOut>20082</frameOut>
      <tcIn>00:10:25:13</tcIn>
      <tcOut>00:13:23:07</tcOut>
      <duration>177760</duration>
      <BroadcastDate>2021-07-24</BroadcastDate>
      <eventDate>2021-07-24</eventDate>
      <resolutionFacet>HD</resolutionFacet>
      <provider>DBC</provider>
      <technicalrightholders>RH_DBC</technicalrightholders>
      <rights>DBC</rights>
      <materialType>DP</materialType>
      <targetFilename>kral_schalke_trenink</targetFilename>
    </cutlistItems>
  </properties>
</EDLXML>

I got the right value from query if i do:

SELECT ExtractValue((SELECT job_xml from cutlist where job_xml is not null LIMIT 1), '//deliveredNum');

But when i change the limit amount i get back: Subquery return more than one row.

CodePudding user response：

extractvalue expects two string arguments. When your subquery returns more than one row, you are not simply passing a string as the first argument (you are passing a set of results).

Instead of calling extractvalue once for your entire query, call it once for every row, like:

SELECT ExtractValue(job_xml, '//deliveredNum') 
FROM cutlist
WHERE job_xml IS NOT NULL