troubleshooting Question

OPENXML issue with XML Data Type

Avatar of D B
D BFlag for United States of America asked on
Microsoft SQL Server
6 Comments1 Solution735 ViewsLast Modified:
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 
	...
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros