How to use OPENXML to load XML data into existing SQL Table?

Question

I am a newbie to OPENXML. But I am trying to load a .XML file into a SQL table that I created for this. I do not receive any errors with this code, but it doesn't insert any records either. This is the table I created in 2008 SQL Server:

CREATE TABLE HOMEROOM(
HOMEROOM_TEACHER INT,
HOMEROOM_NUMBER INT,
ENTITY_ID INT)

And this is the T-SQL code I am trying to execute:

DECLARE @idoc int
DECLARE @xmlDocument varchar(MAX)
DECLARE @Status INT

SET @xmlDocument ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly">
      <s:AttributeType name="c0" rs:name="HOMEROOM-TEACHER" rs:number="1" rs:nullable="true">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" />
      </s:AttributeType>
      <s:AttributeType name="c1" rs:name="HOMEROOM-NUMBER" rs:number="2">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c2" rs:name="ENTITY-ID" rs:number="3">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" />
      </s:AttributeType>
      <s:extends type="rs:rowbase" />
    </s:ElementType>
  </s:Schema>
  <rs:data>
    <z:row c0="22943" c1="101" c2="055" />
    <z:row c0="22929" c1="102" c2="055" />
    <z:row c0="22854" c1="103" c2="055" />
    <z:row c0="22908" c1="104" c2="055" />
    <z:row c0="22881" c1="105" c2="055" />
<z:row c0="22926" c1="Gym2" c2="055" />
<z:row c0="22935" c1="Gym3" c2="055" />
  </rs:data>
</xml>
'
EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, @xmlDocument
SELECT 'sp_xml_preparedocument status=',@Status

select *
FROM   OPENXML (@idoc, '/xml/',1)
         WITH    (
            HOMEROOM_TEACHER          INT    '@C0'
            ,HOMEROOM_NUMBER          VARCHAR(10) '@C1'
            ,ENTITY_ID          VARCHAR(10) 'C2'
                )

--sp_xml_removedocument @idoc

SELECT * FROM HOMEROOM

But after I execute this, I get 0 rows added to HOMEROOM. Any suggestions for how to make this work?

When I execute above, I get error: (1 row(s) affected) Msg 245, Level 16, State 1, Line 627 Conversion failed when converting the nvarchar value 'Gym2' to data type int.

Answer 1

You need to add INSERT INTO HOMEROOM (HOMEROOM_TEACHER, HOMEROOM_NUMBER, ENTITY_ID) above your select and change your SELECT to SELECT HOMEROOM_TEACHER ,HOMEROOM_NUMBER ,ENTITY_ID.

Answer 2

First of all, I would use SQL Server 2005 XQuery over OPENXML - seems easier and cleaner to me.

Second of all - not entirely clear which elements or attributes you want to extract....

Third: you're ignoring the XML namespaces, that's why nothing is working.... they're there for a reason, and you need to pay attention to them!

So I tried something like this here:

DECLARE @input XML = '.....'

;WITH XMLNAMESPACES('urn:schemas-microsoft-com:rowset' AS rs, '#RowsetSchema' AS z)
SELECT
    Nodes.Attr.value('(@c0)[1]', 'INT') AS 'HomeroomTeacher',
    Nodes.Attr.value('(@c1)[1]', 'INT') AS 'HomeroomNumber',
    Nodes.Attr.value('(@c2)[1]', 'INT') AS 'EntityID'
FROM
    @input.nodes('/xml/rs:data/z:row') AS NOdes(Attr)

and I'm getting an output of:

HomeroomTeacher  HomeroomNumber  EntityID
   22943              101           55
   22929              102           55
   22854              103           55
   22908              104           55
   22881              105           55

This might not yet be exactly what you're looking for, but it might be a starting point!

I did:

• define the relevant XML namespaces rs: and z: using thte WITH XMLNAMESPACES construct
• created a "pseudo-table" Nodes with a pseudo-column Attr which basically has one row of XML for each elements that matches that XPath expression
• I then reach into those rows in the pseudo table and I'm able to pull out the relevant bits of information I need

Answer 3

Try this:

EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, 
@xmlDocument, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
       xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
       xmlns:rs="urn:schemas-microsoft-com:rowset" 
       xmlns:z="#RowsetSchema"/>' 
SELECT 'sp_xml_preparedocument status=',@Status 

SELECT * 
FROM OPENXML (@idoc, '/xml/rs:data/z:row',1) 
WITH ( 
   HOMEROOM_TEACHER   INT    '@c0' 
  ,HOMEROOM_NUMBER    INT    '@c1' 
  ,ENTITY_ID          INT    '@c2' 
) 

I did a few things:

1. Added the namespace declaration as the third parameter to sp_xml_preparedocument.
2. Changed the xpath section from '/xml/' to '/xml/rs:data/z:row' to specify the correct position and namespaces in the XML document
3. Changed the @C variables to lower case (@c)

Results were:

HOMEROOM_TEACHER HOMEROOM_NUMBER ENTITY_ID
---------------- --------------- -----------
22943            101             55
22929            102             55
22854            103             55
22908            104             55
22881            105             55

FYI, information about using OPENXML with namespaces can be found here.