OpenXML not parsing data SQL Server 2008 R2

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

Answer 1

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

Answer 2

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

Answer 3

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:

Answer 4

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

Answer 5

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)

Answer 6

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

Answer 7

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)

Answer 8

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

Answer 9

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.