CODEWITHSUNDEEP
xmlsql-server-2008-r2openxmlxml-import
Ryez
Ryez

Reputation: 73

How to access nested elements in xml with OpenXML

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

Upvotes: 2

Views: 3079

Answers (1)

Ryez
Ryez

Reputation: 73

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'

Upvotes: 2

Related Questions