asked on

OPENXML issue with XML Data Type

I am attempting to load data into multiple tables from an XML data source. The data was generated as a data mining tool, using SELECT * FROM table FOR XML RAW, ROOT('tablename'). The output was captured into a variable, then inserted into another XML variable using @XML.modify(). The root node is named <ROOT> and the child node is each table name. Thus, the format of the XML data is:

<ROOT>
<table1>
<row ID="1" .... />
<row ID="2" .... />
</table1>
<table2>
<row ID="1" .... />
<row ID="2" .... />
</table2>
</ROOT>

However, one of the tables has an XML datatype, thus the output for it is:

<mytable>
<row ID="1">
<Data>
<mytable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<CurrentYear>2013</CurrentYear>
<Title>This is the title</Title>
...
</mytable>
<Data>
</row>
</mytable>

The data is loaded using pretty much the same template as below for each table. However, when I attempt to load the table with the XML data type, I get the error: "Element-centric mapping must be used with OPENXML when one of the columns is of type XML."

How would I 'process' the data into this table? Do I somehow have to map each element of the XML file (there are over 300)?

MERGE INTO dbo.mytable TGT 
USING	( 
	SELECT * FROM OPENXML (@HDOC, '/ROOT/mytable/row' ) 
		WITH 
		( 
		ID			INT,
		Data			XML
		) s
	) AS SRC 
	ON	SRC.ID = TGT.ID 
	...

Open in new window

D B

ASKER

BTW, I just want to take the entire contents and insert it into the new table in the two columns, ID and Data. Data contains the whole of the XML data under that node.

Kevin Cross

I think you just need to add @ to the attribute values explicitly.

MERGE INTO dbo.mytable TGT 
USING	( 
	SELECT * FROM OPENXML (@HDOC, '/ROOT/mytable/row' ) 
		WITH 
		( 
		ID			INT '@ID',
		Data			XML 'Data'
		) s
	) AS SRC 
	ON	SRC.ID = TGT.ID 
	...

Open in new window

I do not see anything else right off the bat, but I will keep looking.

EDIT: I corrected a few copy-and-paste errors. The above has been tested and yields two columns. Here is the test script I used.

 DECLARE @idoc int, @doc varchar(1000); 
SET @doc ='
<ROOT>
 <mytable> 
    <row ID="1">
      <Data>
        <mytable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <CurrentYear>2013</CurrentYear>
          <Title>This is the title</Title>
        </mytable>
      </Data>
    </row>
  </mytable>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 

-- SELECT statement that uses the OPENXML rowset provider.
SELECT ID, Data
FROM OPENXML (@idoc, '/ROOT/mytable/row') 
WITH ( 
    ID      INT     '@ID',
    Data    XML     'Data'
) s
;

EXEC sp_xml_removedocument @idoc;

Open in new window

Here is a reference: http://msdn.microsoft.com/en-us/library/ms186918.aspx

D B

ASKER

Using the following I get no results (0 row(s) affected)

DECLARE @idoc int, @doc varchar(max); 
SET @doc ='
<ROOT>
  <SystemConfiguration>
    <row ID="1">
      <Data>
        <SystemConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <IsYearInterval>false</IsYearInterval>
          <ContractEndDate>2013-06-30T00:00:00</ContractEndDate>
          <ShowActiveRecords>false</ShowActiveRecords>
          <GeographicAreaStates>37</GeographicAreaStates>
          <SystemTitle />
          <ProbationEndDays>0</ProbationEndDays>
          <SecondProbationAlertDays>0</SecondProbationAlertDays>
          <FirstProbationAlertDays>0</FirstProbationAlertDays>
          <Probationable>false</Probationable>
          <AllowDuplicateIndividualLinking>true</AllowDuplicateIndividualLinking>
          <SMTPIPaddress>outbound.mail.xx.gov</SMTPIPaddress>
          <PortNumber>25</PortNumber>
          <WebProxyAddress>201.126.107.230</WebProxyAddress>
          <WebProxyPortNumber>80</WebProxyPortNumber>
          <ImmunizationIntegrated>false</ImmunizationIntegrated>
        </SystemConfiguration>
      </Data>
    </row>
  </SystemConfiguration>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 

-- SELECT statement that uses the OPENXML rowset provider.
SELECT ID, Data
FROM OPENXML (@idoc, '/ROOT/mytable/row') 
WITH ( 
    ID      INT     '@ID',
    Data    XML     'Data'
) s
;

EXEC sp_xml_removedocument @idoc;

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

D B

ASKER

My bad. I used 'myTable' as a generic name, and that is what you included in the code. The change works. Thanks.

D B

ASKER

btw, I accepted the generic, but what I did in reality was changed myTabe to SystemConfiguration (which is the table name), since the XML has data for numerous tables.