locked
OpenXML not parsing data SQL Server 2008 R2 RRS feed

  • Question

  • I am trying to parse and XML file using SQL Server 2008 R2 and OpenXML.  I am not getting any error but the values are not being parsed.

    This is the stored procedure code...

    CREATE Procedure [dbo].[UploadFactSheetData](@Inputfile nvarchar(max), @Msg varchar(255) Output)
     as
     Begin
     
        Set NOCOUNT On
       
        Declare @Doc            int,
                -- Core Variables for Cast_Data_Core
                @Pernr          int,
                @Name           nvarchar(100),
                @FName          nvarchar(50),
                @LName          nvarchar(50)
               
        -- Prepare the XML input file for OpenXml usage
        EXEC sp_xml_preparedocument @Doc output, @inputfile, '<ns1:Profile xmlns:ns1="urn:castProfile"/>'
        If @@Error <> 0
        Begin
            Select @Msg = 'Error preparing XML Document'
            return;
        End
       
        Select @Pernr,
            @Name
        From OpenXML (@Doc, 'ns1:Core', 2)
        with (
            Pernr int 'ns1:Pernr',
            Name nvarchar(100) 'ns1:Name')
       
        If @@Error <> 0
        Begin
            Select @Msg = 'Unable to parse core data'
            Return
        End
       
        -- Close the XML Document to save memory
        EXEC sp_xml_removedocument @Doc
        If @@Error <> 0
        Begin
            Select @Msg = 'Error closing the XML Document'
            return;
        End      
       
        -- Parse the Name element into the first and last names
        If Len(@Name) > 0
            Begin
                Select @FName = Left(@Name, CharIndex(' ',@Name) - 1)
                Select @LName = Right(@Name, Len(@Name) - CharIndex(' ', @Name) + 1)
            End
        else --> No name was in the XML
            Begin
                -- Provide an error message
                Select @Msg = 'No Name specified for Pernr: ' + @Pernr
                return
            End
           
        -- Test if Pernr already is in database
        If Exists (Select PernrId From CoreData Where PernrId = @Pernr )
            Begin
                Select @Msg = 'Perner already exists in database' + @Pernr
                return
            End
       
        Insert into CoreData (
            PernrId,
            CoreLastName,
            CoreFirstName )
        values (
            @Pernr,
            @LName,
            @FName )
    End


    This is the XML...

    <ns1:Profile xmlns:ns1="urn:castProfile">
        <ns1:Core>
            <ns1:Pernr>326411</ns1:Pernr>
            <ns1:Name>Phil Deibel</ns1:Name>
            <ns1:Title>Senior Financial Analyst</ns1:Title>
            <ns1:Grade>35</ns1:Grade>
            <ns1:Team>Financial Reporting</ns1:Team>
            <ns1:VP>George Gross</ns1:VP>
            <ns1:Director>George Lucas</ns1:Director>
            <ns1:Leader>Mickey Mouse</ns1:Leader>
            <ns1:Location>Other</ns1:Location>
            <ns1:HireDate>2007-07-01</ns1:HireDate>
            <ns1:TeamStartDate>2007-07-01</ns1:TeamStartDate>
            <ns1:LastPromoDate>2007-07-01</ns1:LastPromoDate>
            <ns1:LastUpdateDate>2007-07-01</ns1:LastUpdateDate>
        </ns1:Core>
        <ns1:Miscellaneous>
            <ns1:Awards>Partners in Excellence, 2005</ns1:Awards>
            <ns1:Involvement>Career Development</ns1:Involvement>
            <ns1:Community>United Way</ns1:Community>
            <ns1:Interests>Theater, Computer Games, Homelessness</ns1:Interests>
        </ns1:Miscellaneous>
        <ns1:Career>
            <ns1:AoFI1>Advisory &amp; Assurance</ns1:AoFI1>
            <ns1:AoFI2>Capital Planning</ns1:AoFI2>
            <ns1:AoFI3>Business Operations</ns1:AoFI3>
            <ns1:Comment>Interested in Consoldations</ns1:Comment>
            <ns1:DomRelo>Yes</ns1:DomRelo>
            <ns1:InterRelo>No</ns1:InterRelo>
        </ns1:Career>
    </ns1:Profile>

    Any help would be appreciated.


    Phil Deibel

    Wednesday, November 14, 2012 1:55 PM

Answers

  • Hi,

    in my demo, I'm showing how to use the XML datatype, OR OPENXML.  You don't need both of them in your proc.  Pick one.  I personally prefer the first one as it's more flexible and doesn't have the potential impact on memory that OPENXML has, but that's just me.

    I also just noticed you are not assigning your column names to variables, eg don't you need this?

    SELECT @Pernr = Pernr, @Name = Name
    FROM OPENXML( @hDoc, 'ns1:Profile/ns1:Core', 2 )
    WITH (
    	Pernr int 'ns1:Pernr',
    	Name nvarchar(100) 'ns1:Name'
    	)


    Or do it this way using the XML datatype:

    ;WITH XMLNAMESPACES( 'urn:castProfile' AS ns1 )
    SELECT
    	@Pernr = c.c.value('(ns1:Pernr/text())[1]', 'INT'),
    	@Name = c.c.value('(ns1:Name/text())[1]', 'NVARCHAR(100)')
    FROM @xml.nodes('ns1:Profile/ns1:Core') c(c)

    • Edited by wBobEditor Wednesday, November 14, 2012 4:35 PM
    • Proposed as answer by wBobEditor Thursday, November 15, 2012 2:03 PM
    • Marked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    Wednesday, November 14, 2012 4:34 PM
    Answerer
  • Hi Phil,

    glad to help.  Regarding your encoding error, you tend to get this if you are using NVARCHAR or VARCHAR with your XML.  I notice in your proc above you are using @xml AS XML.  Are you doing the same in your real proc?  If so, try this instead:

    DECLARE @xml VARCHAR(MAX)

    • Marked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    Thursday, November 15, 2012 2:43 PM
    Answerer

All replies

  • Think your missing the full path ( Profile/Core ).  See demo below for example.  Also consider using the XML datatype and methods available from SQL 2005 onwards, eg

    DECLARE @xml XML
    
    SET @xml = '<ns1:Profile xmlns:ns1="urn:castProfile">
         <ns1:Core>
             <ns1:Pernr>326411</ns1:Pernr>
             <ns1:Name>Phil Deibel</ns1:Name>
             <ns1:Title>Senior Financial Analyst</ns1:Title>
             <ns1:Grade>35</ns1:Grade>
             <ns1:Team>Financial Reporting</ns1:Team>
             <ns1:VP>George Gross</ns1:VP>
             <ns1:Director>George Lucas</ns1:Director>
             <ns1:Leader>Mickey Mouse</ns1:Leader>
             <ns1:Location>Other</ns1:Location>
             <ns1:HireDate>2007-07-01</ns1:HireDate>
             <ns1:TeamStartDate>2007-07-01</ns1:TeamStartDate>
             <ns1:LastPromoDate>2007-07-01</ns1:LastPromoDate>
             <ns1:LastUpdateDate>2007-07-01</ns1:LastUpdateDate>
         </ns1:Core>
         <ns1:Miscellaneous>
             <ns1:Awards>Partners in Excellence, 2005</ns1:Awards>
             <ns1:Involvement>Career Development</ns1:Involvement>
             <ns1:Community>United Way</ns1:Community>
             <ns1:Interests>Theater, Computer Games, Homelessness</ns1:Interests>
         </ns1:Miscellaneous>
         <ns1:Career>
             <ns1:AoFI1>Advisory &amp; Assurance</ns1:AoFI1>
             <ns1:AoFI2>Capital Planning</ns1:AoFI2>
             <ns1:AoFI3>Business Operations</ns1:AoFI3>
             <ns1:Comment>Interested in Consoldations</ns1:Comment>
             <ns1:DomRelo>Yes</ns1:DomRelo>
             <ns1:InterRelo>No</ns1:InterRelo>
         </ns1:Career>
     </ns1:Profile>'
    
    
    -- Use xml datatype 
    ;WITH XMLNAMESPACES( 'urn:castProfile' AS ns1 )
    SELECT
    	c.c.value('(ns1:Pernr/text())[1]', 'INT'),
    	c.c.value('(ns1:Name/text())[1]', 'NVARCHAR(100)')
    FROM @xml.nodes('ns1:Profile/ns1:Core') c(c)
    
    
    DECLARE @hDoc int
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml, '<ns1:Profile xmlns:ns1="urn:castProfile" />'
    
    SELECT *
    FROM OPENXML( @hDoc, 'ns1:Profile/ns1:Core', 2 )
    WITH (
    	Pernr int 'ns1:Pernr',
    	Name nvarchar(100) 'ns1:Name'
    	)
    
    EXEC sp_xml_removedocument @hDoc
    GO

    Wednesday, November 14, 2012 2:28 PM
    Answerer
  • wBob Thanks for your reply, but according to my documentation in online books the Withnamespaces is for outputing XML datatypes (i.e. FORXML).  I am sending the XML to the stored proc as a string from an application and want to load variables that will be used to update numerous tables in the database.  The sample I provided is only a subset of a much larger stored procedure and a much larger XML file.  Both too large to post here, even with this small sample the fields are not parsed in to the SQL variables.  I previously had used 'ns1:Core/ns1:Pernr' which didn't work either.  I will need to modify the larger stored proc once I get an answer or figure out how to make this small sample work.

    Phil Deibel

    Wednesday, November 14, 2012 2:57 PM
  • Hi Phil,

    have you actually tried running my demo script?  It's worked on my SQL2008R2 instance, giving two resultsets, one showing the newer method of using the XML datatype and methods, the other showing the OPENXML method working.  I'm using the full address Profile/Core.

    Regarding WITH XMLNAMESPACES only being for outputting XML, that is incorrect.  You can use it for creating or shredding xml when namespaces are involved.

    Try the script posted above and we'll work forward from there:

    Wednesday, November 14, 2012 3:07 PM
    Answerer
  • Yes wBob, I did try your code and in SQL Manager it works like a champ, but when translated to the stored procedure it throws an exception... {"The specified cast from a materialized 'System.Int32' type to the 'System.String' type is not valid."} without changing the stored procedure signature.

    Here is the modified stored procedure...

     CREATE Procedure [dbo].[UploadFactSheetData](@Inputfile nvarchar(max), @Msg varchar(255) Output)
     as
     Begin
     
        Set NOCOUNT On
       
        Declare @Doc            int,
                -- Core Variables for Cast_Data_Core
                @Pernr          int,
                @Name           nvarchar(100),
                @FName          nvarchar(50),
                @LName          nvarchar(50),
                @XML            XML
               
        Set @XML = @Inputfile;
       
        ;WITH XMLNAMESPACES( 'urn:castProfile' AS ns1 )
        SELECT
        c.c.value('(ns1:Pernr/text())[1]', 'INT'),
        c.c.value('(ns1:Name/text())[1]', 'NVARCHAR(100)')
        FROM @xml.nodes('ns1:Profile/ns1:Core') c(c)

        -- Prepare the XML input file for OpenXml usage
        EXEC sp_xml_preparedocument @Doc output, @XML, '<ns1:Profile xmlns:ns1="urn:castProfile"/>'
        If @@Error <> 0
        Begin
            Select @Msg = 'Error preparing XML Document'
            return;
        End
              
        Select @Pernr,
            @Name
        From OpenXML (@Doc, 'ns1:Profile/ns1:Core', 2)
        with (
            Pernr int 'ns1:Pernr',
            Name nvarchar(100) 'ns1:Name')
       
        If @@Error <> 0
        Begin
            Select @Msg = 'Unable to parse core data'
            Return
        End
       
        -- Close the XML Document to save memory
        EXEC sp_xml_removedocument @Doc
        If @@Error <> 0
        Begin
            Select @Msg = 'Error closing the XML Document'
            return;
        End      
       
        -- Parse the Name element into the first and last names
        If Len(@Name) > 0
            Begin
                Select @FName = Left(@Name, CharIndex(' ',@Name) - 1)
                Select @LName = Right(@Name, Len(@Name) - CharIndex(' ', @Name) + 1)
            End
        else --> No name was in the XML
            Begin
                -- Provide an error message
                Select @Msg = 'No Name specified for Pernr: ' + @Pernr
                return
            End
           
        -- Test if Pernr already is in database
        If Exists (Select PernrId From CoreData Where PernrId = @Pernr )
            Begin
                Select @Msg = 'Perner already exists in database' + @Pernr
                return
            End
       
        Insert into CoreData (
            PernrId,
            CoreLastName,
            CoreFirstName )
        values (
            @Pernr,
            @LName,
            @FName )
    End


    Phil Deibel

    Wednesday, November 14, 2012 4:20 PM
  • Hi,

    in my demo, I'm showing how to use the XML datatype, OR OPENXML.  You don't need both of them in your proc.  Pick one.  I personally prefer the first one as it's more flexible and doesn't have the potential impact on memory that OPENXML has, but that's just me.

    I also just noticed you are not assigning your column names to variables, eg don't you need this?

    SELECT @Pernr = Pernr, @Name = Name
    FROM OPENXML( @hDoc, 'ns1:Profile/ns1:Core', 2 )
    WITH (
    	Pernr int 'ns1:Pernr',
    	Name nvarchar(100) 'ns1:Name'
    	)


    Or do it this way using the XML datatype:

    ;WITH XMLNAMESPACES( 'urn:castProfile' AS ns1 )
    SELECT
    	@Pernr = c.c.value('(ns1:Pernr/text())[1]', 'INT'),
    	@Name = c.c.value('(ns1:Name/text())[1]', 'NVARCHAR(100)')
    FROM @xml.nodes('ns1:Profile/ns1:Core') c(c)

    • Edited by wBobEditor Wednesday, November 14, 2012 4:35 PM
    • Proposed as answer by wBobEditor Thursday, November 15, 2012 2:03 PM
    • Marked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    Wednesday, November 14, 2012 4:34 PM
    Answerer
  • Thank you wBob, you were correct that the issue wasn't that the fields were not parsing it was I had forgotten to equate the selected fields to the variables.

    The proc now processes the data, but I had to remove the line...

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

    OpenXML doesn't like it and throws the exception...

    "Switch from current encoding to specified encoding not supported"

    If you have an answer as to this issue (other than removing the line from the XML files) it would solve all my issues.


    Phil Deibel

    Thursday, November 15, 2012 2:33 PM
  • Hi Phil,

    glad to help.  Regarding your encoding error, you tend to get this if you are using NVARCHAR or VARCHAR with your XML.  I notice in your proc above you are using @xml AS XML.  Are you doing the same in your real proc?  If so, try this instead:

    DECLARE @xml VARCHAR(MAX)

    • Marked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    Thursday, November 15, 2012 2:43 PM
    Answerer
  • I was using nvarchar(max) but changed it to varchar(max) I think that will work for our data.  I had read there was a 8000 char max size on the varchar.  The varchar(max) handles the UTF-8 encoding though.  I had originally used NTEXT but read that was deprecated now so had switched to nvarchar(max).

    Thanks again for all your help.


    Phil Deibel

    • Marked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    • Unmarked as answer by Phil Deibel Thursday, November 15, 2012 4:05 PM
    Thursday, November 15, 2012 4:04 PM
  • Thanks Phil  The old varchar(8000) / nvarchar(4000) limit were in SQL 2000.  The limit for the lob datatypes ( eg VARCHAR(MAX), NVARCHAR(MAX), XML ) is now 2GB.

    NTEXT is a bit of a pain to work with so I'm glad you've abandoned it.

    Thursday, November 15, 2012 4:52 PM
    Answerer