I have to import xml files to to a SQL Server Database. I am using OPENXML, but since the files have two different layouts (because it was updated a couple years ago) I don’t know any way to import both layouts on the same function.

My current code is like this:

FROM OPENXML(@idoc, N'ROOT/event1 /event2 /event3 /event4 /event5 /event6')

The code is working for the newer layout.

My problem is that the event3 and event4 elements on the XML only appear in files that were generated after october 2022.

Is there a way to point that those two elements are optional? Any way the funcion can "jump" from event2 to event5 if event3 and event4 does not exist?

Thank you for your attention

>Solution :

You should be able to do something like this:

declare @x xml = '<ROOT><event1><event2><event3><event4><event5><event6>test</event6></event5></event4></event3></event2></event1></ROOT>'
declare @h int
exec sys.sp_xml_preparedocument @h output, @x
select *
FROM OPENXML(@h, N'ROOT/event1/event2//event5/event6')

exec sys.sp_xml_removedocument @h

OPENXML is a bit old-school though, you might wanna look into something newer like the nodes function.