Answered by:
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
Friday, February 5, 2010 10:48 AM
Answers
-
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.aspxxml 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/Friday, February 5, 2010 11:18 AM
All replies
-
This should probably have gone into the XML forum. How do I get it moved?!
Friday, February 5, 2010 10:57 AM -
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!Friday, February 5, 2010 10:59 AM -
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.aspxxml 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/Friday, February 5, 2010 11:18 AM -
The nodes method worked perfectly. I bow down to your XML genius. Thankyou so much!Friday, February 5, 2010 12:11 PM
-
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!!
Thursday, June 10, 2010 9:57 PM