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</fe ature_type >
<feature_type>Wind farm</feature_type>
</feature_types>
<authority>Atkins</authori ty>
<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
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/
However, if I add the next element, i.e. 'preliminary/ordered_list/
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"
<region>SCOTLAND</region>
<sub_region>East Coast</sub_region>
<vicinities>
<vicinity>Aberdeen SE</vicinity>
</vicinities>
<feature_types>
<feature_type>Moorings</fe
<feature_type>Wind farm</feature_type>
</feature_types>
<authority>Atkins</authori
<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/
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/
WITH
(
nm_number varchar(50) '../../../../@nm_number',
[year] varchar(50) '../../../../@year',
paragraph varchar(1024) '../../paragraph',
width varchar(10) '.'
)
--------------------------
Many thanks in advance,
Liz
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers 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.
as TABLE seems to be direct child.