How to access nested elements in xml with OpenXML

Question

I'm trying to import data from a rather large xml file to an SQL Server. After some online searching I settled upon using OpenXML. The query I have so far is

DECLARE @doc INT
DECLARE @xml XML
SELECT @xml = evnt
FROM OPENROWSET (BULK 'c:\archive.xml', SINGLE_BLOB) AS Import(evnt)

EXEC sp_xml_preparedocument @doc OUTPUT, @xml

SELECT *
FROM OPENXML( @doc, '/Events/eventData/event', 2)
WITH (
  id varchar(max)
)
EXEC sp_xml_removedocument @doc

In this way I'm able to get elements listed inside 'event', but how would I get an element one more level down from 'event'. For example how would I get the 'CustId' tag below?

<event>
    <custromAttribute>
        <CustId>...

The items inside 'event' is mainly needed hence the path cant be changed. Also any other approaches would be nice too. I need it to run from SQL Server so can't use SSIS or other similar external tools. XQuery seems to take a lot of time.

I'm using SQL Server 2008 R2

Why did you choose OpenXML instead of the native SQL Server XQuery support? Seems a lot easier to use and more flexible... — marc_s
– marc_s, Commented Apr 18, 2013 at 9:13
I read in a lot of places that OpenXML is better with larger files, though it is very memory intensive. The files to be parsed can easily go over 100MB. — Ryez
– Ryez, Commented Apr 18, 2013 at 11:25

Ryez · Accepted Answer · 2013-04-19 05:22:00Z

2

If anyone is interested the solution to the above question was simple enough.

SELECT *
FROM OPENXML( @doc, '/Events/eventData/event')
WITH (
  ID varchar(max) 'id'
  CustID varchar(20) 'customAttribute/CustId',
  AttributeID varchar(20) '@id'
  SampleID varhcar(20) 'Ele1/Ele2[3]/id'
)

The values in quotes should match the name in the xml doc exactly. '@' is used to select attributes in the element. Going back a level is done with '../'.

The SampleID fetches 'id' within the third 'Ele2' element inside 'Ele1'

edited Apr 19, 2013 at 5:22

answered Apr 19, 2013 at 5:05

Ryez

732 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to access nested elements in xml with OpenXML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related