Link to home
Start Free TrialLog in
Avatar of Liz Scott
Liz Scott

asked on

OPENXML not returning data from file with mulitple nested elements / attributes

Good afternoon,

I have a weekly XML file that I need to load into a SQL database table.  

I do not need all the content, so I am using OPENXML to navigate to the my element, which in the first example below is 'preliminary/ordered_list/list_item/paragraph' and which returns data.

However, if I add the next element, i.e. 'preliminary/ordered_list/list_item/paragraph/TABLE', no data is returned.

Please see below the SQL script I am using to test an extract from this file, with both versions of the OPENXML statement to return the data.

I am pretty new to XML and OPENXML but have managed with other files okay so far, but this one has me stumped, so I would be really grateful for any help!

------------------------------------------------------------------------------------------------------------------------------------------------------------
declare @xml XML

set @xml=N'<preliminary geographical_area_code="2" nm_number="2911" year="18">
    <region>SCOTLAND</region>
    <sub_region>East Coast</sub_region>
    <vicinities>
      <vicinity>Aberdeen SE</vicinity>
    </vicinities>
    <feature_types>
      <feature_type>Moorings</feature_type>
      <feature_type>Wind farm</feature_type>
    </feature_types>
    <authority>Atkins</authority>
    <ordered_list Style="Arabic">
      <list_item>
        <paragraph>Work has commenced on the construction of Kincardine Offshore Floating Wind Farm. The construction area is bounded by the following positions:</paragraph>
        <TABLE width="100%" border="1">
          <TBODY>
            <TR>
              <TD width="100%">
                <xparagraph>
                  <location>
                    <latitude>
                      <degrees>57</degrees>
                      <minutes>04</minutes>
                      <decimals>75</decimals>
                      <hemisphere>N</hemisphere>
                    </latitude>
                    <longitude>
                      <degrees>1</degrees>
                      <minutes>49</minutes>
                      <decimals>50</decimals>
                      <hemisphere>W</hemisphere>
                    </longitude>
                  </location>
                </xparagraph>
              </TD>
            </TR>
           </TBODY>
        </TABLE>
      </list_item>
       <list_item>
        <paragraph>      Cable laying operations will also be conducted between Kincardine Floating Wind Farm and Aberdeen.</paragraph>
      </list_item>
      <list_item>
        <paragraph>      Vessels should navigate with caution in the area.</paragraph>
      </list_item>
      <list_item>
        <paragraph>Charts will be updated when full details are available.</paragraph>
        <paragraph>(ETRS89 DATUM)</paragraph>
      </list_item>
    </ordered_list>
  </preliminary>';
 
 
-- create variables for manipulating xml data
DECLARE @hDoc AS INT, @SQL NVARCHAR (MAX)
-- prepare the content
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


-- the statement below returns a result
SELECT *
FROM OPENXML(@hDoc, 'preliminary/ordered_list/list_item/paragraph')
WITH
(
      nm_number varchar(50) '../../../@nm_number',
      [year] varchar(50) '../../../@year',
      paragraph varchar(1024) '../paragraph'
)

-- the statement below does not return a result!
SELECT *
FROM OPENXML(@hDoc, 'preliminary/ordered_list/list_item/paragraph/TABLE')
WITH
(
      nm_number varchar(50) '../../../../@nm_number',
      [year] varchar(50) '../../../../@year',
      paragraph varchar(1024) '../../paragraph',
      width varchar(10) '.'
)
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Many thanks in advance,

Liz
Avatar of Valliappan AN
Valliappan AN
Flag of India image

Please try preliminary/ordered_list/list_item/TABLE

as TABLE seems to be direct child.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.