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:

    <row ID="1" .... />
    <row ID="2" .... />
    <row ID="1" .... />
    <row ID="2" .... />

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

    <row ID="1">
        <mytable xmlns:xsi="" xmlns:xsd="">
          <Title>This is the title</Title>

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 
	SELECT * FROM OPENXML (@HDOC, '/ROOT/mytable/row' ) 
		ID			INT,
		Data			XML
		) s
	) AS SRC 

Open in new window

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
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