Cannot parse using OPENXML with namespace

Question

Hi all

I've been using the OPENXML method to import XML data into tables but I have a particular XML file with namespace information that I can't seem to parss.

I don't know too much about XML I'm afraid. From various Googling it appears I need to use the third property of the #sp_xml_preparedocument, or perhaps some other workaround. I've been trying to figure this out for hours now.

The file I'm trying to parse can be found at http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml. For testing just save this to C:\Currencies.xml.

DECLARE @filePath VARCHAR(MAX)
SET @filepath = 'C:'

-- Prepare the XML file ready for processing
DECLARE @XmlDocument XML
DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #XMLImport (XMLDocument XML)
SET @SQL = 'INSERT INTO #XMLImport SELECT BulkColumn FROM OPENROWSET(BULK ''' + @filePath + '\Currencies.xml'', SINGLE_BLOB) AS x'
EXEC (@SQL)
SET @XmlDocument = (SELECT TOP 1 XMLDocument FROM #XMLImport)
DROP TABLE #XMLImport
SELECT @XmlDocument
DECLARE @XmlDocumentHandle INT
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
	
SELECT    *
FROM       OPENXML (@XmlDocumentHandle, '/Cube/Cube/Cube',1)
            WITH ( [currency]  VARCHAR(4),[rate] DECIMAL(12,4))

EXEC sp_xml_removedocument @XmlDocumentHandle

Answer 1

This should probably have gone into the XML forum. How do I get it moved?!

Answer 2

This should probably have gone into the XML forum. How do I get it moved?!

You are in Transact SQL forum :D
wait for some time, some experts will answer!

---

Chase Excellence - Success Will Follow!

Answer 3

To use namespaces with OPENXML / sp_xml_preparedocument simply pass the namespaces as the third argument to sp_xml_preparedocument, eg:

EXEC sp_xml_preparedocument 
	@XmlDocumentHandle OUTPUT, 
	@XmlDocument, 
	'<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns:t="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" />'

SELECT *
FROM OPENXML (@XmlDocumentHandle, 'gesmes:Envelope/t:Cube/t:Cube/t:Cube',1)
WITH ( [currency]  VARCHAR(4),[rate] DECIMAL(12,4))

EXEC sp_xml_removedocument @XmlDocumentHandle

However as you're using the XML and VARCHAR(MAX) data-types you must be using either SQL 2005 or 2008.  Therefore you can also use the methods of the XML data-type to do this, eg:

 

;WITH XMLNAMESPACES ( 'http://www.gesmes.org/xml/2002-08-01' AS gesmes, DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref' )
SELECT 
	x.c.value( '@currency', 'VARCHAR(4)' )  AS currency,
	x.c.value( '@rate', 'DECIMAL(12,4)' ) AS rate
FROM @XmlDocument.nodes('gesmes:Envelope/Cube/Cube/Cube') x(c)

See these articles:

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

xml Data Type Methods
http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx

Come over to the SQL Server XML forum for all your SQL Server and XML questions!

http://social.msdn.microsoft.com/forums/en-US/sqlxml/threads/

Answer 4

The nodes method worked perfectly. I bow down to your XML genius. Thankyou so much!

Answer 5

I just got this code to work from the management Studio but I'm getting an error calling the proc from my VB code now.

 

-- Open the XML Doc

 

SET @ErrorMsg = 'Error Opening XML Doc'

 

EXEC sp_xml_preparedocument @handle OUTPUT, @InputDoc , ' <SupressionDetails xmlns:a="INFOSUITE/ONLINEORDER"/>'

 

 

SET @ErrorMsg = 'Error Inserting XML Into temp table.'

 

 

-- Insert the XML Doc into the temp table

 

INSERT INTO

#TempConsumerRecords

 

 

(

FName

,

LName

,

PCode

,

StreetNo

,

StreetName

,

SuiteNo

,

AreaCode

,

Phone

,

Address1

,

Address2

,

City

,

Prov

 

)

 

SELECT *

 

FROM OPENXML (@handle, '//a:SupressionDetail', 2) WITH

 

 

(

[a:FirstName]

VARCHAR(15),

[a:LastName]

VARCHAR(20),

[a:PCode]

VARCHAR(7),

[a:StreetNumber]

VARCHAR(8),

[a:StreetName]

VARCHAR(35),

[a:UnitNumber]

VARCHAR(9),

[a:AreaCode]

VARCHAR(3),

[a:Phone]

VARCHAR(8),

[a:Address1]

VARCHAR(40) ,

[a:Address2]

VARCHAR(40),

[a:City]

VARCHAR(30),

[a:Prov]

VARCHAR(2)

 

)

Thanks!!