6 Replies Latest reply on Mar 12, 2020 5:23 PM by 3625952

    How To Extract Data From XML

    3625952

      Hi everyone.  I know that this has been asked before, but I am still having problems extracting data from the XML files in my database.  I have a camera calibration table called CMR_CLBRTN_CNFG, which has 2 rows, WRKSTN_NAME and CMR_CLBRTN_FILE, which is an XML file.  The table has around 150 rows, with each having it's own XML file.  I wanted to extract the camera calibration data (ID, Name, Tag, Data Type, Data Value and Is Current Value) from each XML file.  A sample XML file is :

       

      <?xml version="1.0"?>

      <Root>

        <CanonRebelT3i-stand>

          <PropList>

            <Timestamp>2019/01/03 17:51:54</Timestamp>

            <Property>

              <Id>1029</Id>

              <Name>Aperture</Name>

              <Tag>5.6</Tag>

              <DataType>16</DataType>

              <DataValue>48</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

            <Property>

              <Id>1026</Id>

              <Name>ISO Speeds</Name>

              <Tag>100</Tag>

              <DataType>16</DataType>

              <DataValue>72</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

            <Property>

              <Id>1030</Id>

              <Name>Shutter Speed</Name>

              <Tag>1/125</Tag>

              <DataType>16</DataType>

              <DataValue>112</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

          </PropList>

        </CanonRebelT3i-stand>

        <CanonRebelT3i-sit>

          <PropList>

            <Timestamp>2019/01/03 17:51:54</Timestamp>

            <Property>

              <Id>1026</Id>

              <Name>ISO Speeds</Name>

              <Tag>100</Tag>

              <DataType>16</DataType>

              <DataValue>72</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

            <Property>

              <Id>1030</Id>

              <Name>Shutter Speed</Name>

              <Tag>1/125</Tag>

              <DataType>16</DataType>

              <DataValue>112</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

            <Property>

              <Id>1029</Id>

              <Name>Apreture</Name>

              <Tag>6.7</Tag>

              <DataType>16</DataType>

              <DataValue>52</DataValue>

              <IsCurrentValue>False</IsCurrentValue>

            </Property>

          </PropList>

        </CanonRebelT3i-sit>

      </Root>

       

      I was hoping that someone could help me extract the data.  We are using Oracle 11.2.0.4.  Thank you so much in advance.

        • 1. Re: How To Extract Data From XML
          cormaco

          Here is an example:

          with calibration(CMR_CLBRTN_FILE) as (
          select xmltype(
          '<?xml version="1.0"?>
          <Root>
              <CanonRebelT3i-stand>
                  <PropList>
                      <Timestamp>2019/01/03 17:51:54</Timestamp>
                      <Property>
                          <Id>1029</Id>
                          <Name>Aperture</Name>
                          <Tag>5.6</Tag>
                          <DataType>16</DataType>
                          <DataValue>48</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                      <Property>
                          <Id>1026</Id>
                          <Name>ISO Speeds</Name>
                          <Tag>100</Tag>
                          <DataType>16</DataType>
                          <DataValue>72</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                      <Property>
                          <Id>1030</Id>
                          <Name>Shutter Speed</Name>
                          <Tag>1/125</Tag>
                          <DataType>16</DataType>
                          <DataValue>112</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                  </PropList>
              </CanonRebelT3i-stand>
              <CanonRebelT3i-sit>
                  <PropList>
                      <Timestamp>2019/01/03 17:51:54</Timestamp>
                      <Property>
                          <Id>1026</Id>
                          <Name>ISO Speeds</Name>
                          <Tag>100</Tag>
                          <DataType>16</DataType>
                          <DataValue>72</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                      <Property>
                          <Id>1030</Id>
                          <Name>Shutter Speed</Name>
                          <Tag>1/125</Tag>
                          <DataType>16</DataType>
                          <DataValue>112</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                      <Property>
                          <Id>1029</Id>
                          <Name>Apreture</Name>
                          <Tag>6.7</Tag>
                          <DataType>16</DataType>
                          <DataValue>52</DataValue>
                          <IsCurrentValue>False</IsCurrentValue>
                      </Property>
                  </PropList>
              </CanonRebelT3i-sit>
          </Root>') from dual)
          select camera, id, name, tag, datatype, datavalue, iscurrentvalue
          from calibration,
          xmltable(
              '/Root/*'
              passing CMR_CLBRTN_FILE
              columns 
                  camera varchar2(20) path 'name()',
                  properties xmltype  path 'PropList/Property'
          ),
          xmltable(
              'Property'
              passing properties
              columns
                  id   number (4)   path 'Id',
                  name varchar2(20) path 'Name',
                  tag  varchar2(10) path 'Tag',
                  datatype number(2) path 'DataType',
                  datavalue varchar2(20) path 'DataValue',
                  iscurrentvalue varchar2(10) path 'IsCurrentValue'
          )
          
          
          CAMERA                       ID NAME                 TAG          DATATYPE DATAVALUE            ISCURRENTV
          -------------------- ---------- -------------------- ---------- ---------- -------------------- ----------
          CanonRebelT3i-stand        1029 Aperture             5.6                16 48                   False     
          CanonRebelT3i-stand        1026 ISO Speeds           100                16 72                   False     
          CanonRebelT3i-stand        1030 Shutter Speed        1/125              16 112                  False     
          CanonRebelT3i-sit          1026 ISO Speeds           100                16 72                   False     
          CanonRebelT3i-sit          1030 Shutter Speed        1/125              16 112                  False     
          CanonRebelT3i-sit          1029 Apreture             6.7                16 52                   False     
          
          6 rows selected. 
          
          
          
          • 2. Re: How To Extract Data From XML
            3625952

            Thank you so much for your response.  I guess in my case, I will have to create a stored procedure which loops though all of the XML files?

            • 3. Re: How To Extract Data From XML
              odie_63

              3625952 wrote:

               

              I guess in my case, I will have to create a stored procedure which loops though all of the XML files?

              You can create a procedure if you want, but you certainly don't have to loop, unless each XML must be processed individually for some (business) reasons.

              For example, you can directly use your table and extract everything in one go :

              select t.WRKSTN_NAME

                   , x1.camera

                   , x2.id

                   , x2.name

                   , x2.tag

                   , x2.datatype

                   , x2.datavalue

                   , x2.iscurrentvalue 

              from CMR_CLBRTN_CNFG t 

                 , xmltable( 

                        '/Root/*' 

                        passing t.CMR_CLBRTN_FILE 

                        columns  

                            camera varchar2(20) path 'name()', 

                            properties xmltype  path 'PropList/Property' 

                    ) x1 

                 ,  xmltable( 

                        'Property' 

                        passing x1.properties 

                        columns 

                            id   number (4)   path 'Id', 

                            name varchar2(20) path 'Name', 

                            tag  varchar2(10) path 'Tag', 

                            datatype number(2) path 'DataType', 

                            datavalue varchar2(20) path 'DataValue', 

                            iscurrentvalue varchar2(10) path 'IsCurrentValue' 

                    ) x2

              ;

              • 4. Re: How To Extract Data From XML
                3625952

                Thanks for your reply.  I tried your query, and I am getting a ORA-00932: inconsistent datatypes: expected - got BLOB error.

                • 5. Re: How To Extract Data From XML
                  cormaco

                  ORA-00932: inconsistent datatypes: expected - got BLOB

                  Is your column CMR_CLBRTN_FILE a BLOB? Odie and I assumed it's XMLTYPE.

                  If so it must be converted to XMLTYPE first like this:

                   

                  passing xmltype(CMR_CLBRTN_FILE,NLS_CHARSET_ID('AL32UTF8'))
                  

                   

                  If the original xmlfile was in a different charcterset than UTF-8, you have to replace 'AL32UTF8' with the appropriate value.

                  • 6. Re: How To Extract Data From XML
                    3625952

                    I'm really sorry for misleading you.  It is a BLOB column.  Either way, it is working now exactly like I hoped.  I wish I could give you both the Correct Answer vote, since you were both awesome.  Thank you so much for everything.  This is fantastic.