locked
Cannot parse using OPENXML with namespace RRS feed

  • 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.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/

    • Proposed as answer by JGSB Friday, February 5, 2010 11:46 AM
    • Marked as answer by Dustin007 Friday, February 5, 2010 12:09 PM
    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.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/

    • Proposed as answer by JGSB Friday, February 5, 2010 11:46 AM
    • Marked as answer by Dustin007 Friday, February 5, 2010 12:09 PM
    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