4 of 11
added 2 characters in body
Dan
  • 167
  • 2
  • 9

XML/SQL Server 2008 Error: XQuery...Cannot implicitly atomize or apply 'fn:data()' to complex content elements

Disclaimer! I'm new to a lot of this. I've had fairly limited self taught exposure to SQL server, VBA, XPATH, XSD, XML and a year of formal training in logic (Java). My job has migrated me into a unique position a year or so ago to getting off and on exposure to SQL and XML so I'm still quite a novice. The things I post/ask may be very naïve and if so, I apologize and welcome frank, harsh, criticism, suggestions about function and appropriateness of form and good hard copy references (I don't mean the Microsoft docs because I rarely can figure heads or tails of them). So on to it!

I've been getting this error with a project I'm working on and although there are some posts about it here and elsewhere, nothing seems to be helping. My guess is I'm having an XPATH expression problem...initially.

XQuery [XMLTestTable.DATA.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element({urn:MyFile-schema}:SUBUNITPRICE,xs:anyType) *'.

As you can probably guess this is an XML import/conversion to relational table project. I've found quite a bit on the net about this type of thing but very little explanation of what much of it means (although I have some educated guesses at most of it).

I'll start here. Schemas:

Some of the time people will import a schema first, sometimes they don't and just roll with it. The way I understand schemas, they are for 1: validation of the xml document to some sort of standard to make sure everything runs/imports/exports smoothly 2: potentially increase efficiency of importing/exporting/editing of a file. Although I find that validating the document itself adds time to the import (I assume it's simply extra steps), downstream queries maybe more efficient although I haven't gotten that far. Either way, I thought as a matter of course and practice it was a good idea. So here is my schema (this was hand transcribed so if you see something bad, please yell at me!). Also, I use a node called SUBUNITPRICE, please understand that this isn't actually a monetary unit and I've changed some of the node names to keep things a little more confidential. Just know that this node value is a text value that can contain numbers and symbols.

<?xml version="1.0" encoding="UTF-8"?>
 <xs:schema  xmlns:xs= "http://www.w3.org/2001/XMLSchema" targetNamespace="urn:MyFile-schema" xmlns="urn:MyFile-schema" elementFormDefault="qualified">
         <xs:element name="MyFile">
               <xs:complexType>
                     <xs:sequence>
                           <xs:element name="FIELD1" type="xs:double"/>
                           <xs:element name="FIELD2" type="xs:string"/>
                           <xs:element name="FIELD3" type="xs:string"/>
                           <xs:element name="FIELD4" type="xs:string"/>
                           <xs:element name="FIELD5" type="xs:string"/>
                           <xs:element name="FIELD6" type="xs:dateTime"/>
                           <xs:element name="GROUP" maxOccurs="unbounded">
                                 <xs:complexType>
                                       <xs:sequence>
                                             <xs:element name="GROUPID" type="xs:string"/>
                                             <xs:element name="GROUPCATEGORY" type="xs:string"/>
                                             <xs:element name="UNIT" maxOccurs="unbounded">
                                                   <xs:complexType>
                                                         <xs:sequence>
                                                               <xs:element name=" UNITNAME" type="xs:string"/>
                                                               <xs:element name="REVIEWER" type="xs:string"/>
                                                               <xs:element name="DATEANDTIME" type="xs:dateTime"/>
                                                               <xs:element name="SUBUNIT" maxOccurs="unbounded">
                                                                     <xs:complexType>
                                                                           <xs:sequence>
                                                                                 <xs:element name=" SUBUNITPRICE" type="xs:string"/>
                                                                           </xs:sequence>
                                                                     </xs:complexType>
                                                               </xs:element>
                                                         </xs:sequence>
                                                         <xs:attribute name="MULTIENTRY" type="xs:string"/>
                                                         <xs:attribute name="PARTIALUNIT" type="xs:string"/>
                                                         <xs:attribute name="KIT" type="xs:string"/>
                                                   </xs:complexType>
                                             </xs:element>
                                       </xs:sequence>
                                       <xs:attribute name="FULL" type="xs:string"/>
                                       <xs:attribute name="VER" type="xs:string"/>
                                 </xs:complexType>
                           </xs:element>
                     </xs:sequence>
                     <xs:attribute name="MyFile" type="xs:string"/>
               </xs:complexType>
         </xs:element>
   </xs:schema>

My XML. Comments are in it after the GROUP, UNITNAME and SUBUNIT nodes for references to node length, structure and potential XML file size (potentially 100k GROUP nodes for a file of about 500-600 MB each). Ellipses (...) just mark that there is an iteration of that specific node and the number of iterations I hope you find implicit in the node values. It may be that several of these files have to be imported into a SQL Server db at any given time.

<?xml version="1.0" encoding="utf-8"?>
<MyFile xmlns="urn:MyFile-schema">
    <FIELD1>FileName</ FIELD1>
    <FIELD2>Foo</ FIELD2>
    <FIELD3>Bar</ FIELD3>
    <FIELD4>Upload</ FIELD4>
    <FIELD5>UserName</ FIELD5>
    <FIELD6>UploadTime</ FIELD6>
    <GROUP VER="Yes" FULL="false">     ‘<---- There can be up to 100K of these (maybe more soon) ---identified by child node GROUPID
        <GROUPID>GrName1</ GROUPID>   
        <GROUPCATEGORY>MyCategory</ GROUPCATEGORY>
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”>
            <UNITNAME>Unit1</ UNITNAME>    ‘<---- 12 to 35 possible in each GROUP NODE
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> ‘<---- 2 in each UNIT NODE most of time – will be same tag SUBUNIT
                <SUBUNITPRICE>11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> ‘<-sometimes 3 in intermittent unit nodes but rarely included in file
                < SUBUNITPRICE >20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
        ...
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”>
            <UNITNAME>Unit23</ UNITNAME> 
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> 
                <SUBUNITPRICE>$11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> 
                <SUBUNITPRICE>$20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
    </GROUP>
    <GROUP VER="Yes" FULL="false">
        <GROUPID>GroupName100,000</ GROUPID>   
        ...
    </GROUP>
</MyFile>

Here's what I've been doing with it.

Schema import:

--Import the schema
IF EXISTS(SELECT * FROM sys.xml_schema_collections where [name] = 'XMLSchema')
DROP XML SCHEMA COLLECTION[XMLSchema]

DECLARE @MySchema XML
SET @MySchema = 
(SELECT * FROM OPENROWSET
    (BULK 'C:\Path\Schema.xsd', SINGLE_BLOB) AS xmlData
)
CREATE XML SCHEMA COLLECTION[XMLSchema] AS @MySchema

Load the file. This takes about 2 minutes for a 50k GROUP Node file, I have no idea how long it will take for 100k. I'd like to speed this up. I add an index to the xml column. I'm not quite sure about this part except that I picked it up as a bit of advice that it speeds things up a bit with queries downstream and I know it runs.

I understand that if I add secondary indices to the XML column it could really speed things up. I have no idea where to start with that. If anyone has got some suggestions for a reference, or a quick addition, I'd appreciate the input.

CREATE TABLE XMLTestTable
( 
ID INT IDENTITY PRIMARY KEY,
DATA xml(CONTENT MyXmlSchema)
)

INSERT INTO XMLTestTable
(DATA)
 
SELECT CONVERT(XML, BulkColumn) as BulkColumn


--import an xml file into the column
FROM OPENROWSET(BULK 'C:\Path\FileName.XML', SINGLE_BLOB) as x

CREATE PRIMARY XML INDEX PXML_DATA 
ON XMLTestTable (DATA)

Next: I need to put the GROUPID and both SUBUNITPRICEs for each GROUPID into a table, Ill call it GROUPTABLE and it hopefully look like this:

|ID    |GROUPID|UNIT1_SUBUNITPRICE_1|UNIT1_SUBUNITPRICE_2|……|UNIT23_SUBUNITPRICE_2|
|1     |GrName1|11.50               |20.00               |……|25.00                |
|2     |GrName2|1.00                |32.41               |……|45.51                |

So I create the tables:

CREATE TABLE GROUPTABLE
(
ID int IDENTITY(1,1) PRIMARY KEY,
GROUPID varchar(20),
UNIT1_SUBUNITPRICE_1 varchar(7),
UNIT1_SUBUNITPRICE_2 varchar(7),
…
UNIT23_SUBUNITPRICE_1 varchar(7),
UNIT23_SUBUNITPRICE_2 varchar(7)
)

Now the part that generates the error! If I start by just trying to insert the index and the groupid, it works great! Like this:

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')

INSERT INTO GROUPTABLE
Select
    t.b.value('GROUPID[1]', 'varchar(20)') AS GROUPID 
    FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

...and then the problem. Let's try to add the subunitprices:

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')
    
INSERT INTO GROUPTABLE

Select 
    t.b.value('UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]', 'varchar(7)') AS SUBUNITPRICE_1
FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

Generating the error I mentioned way back at the beginning.

XQuery [XMLTestTable.DATA.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element({urn:MyFile-schema}:SUBUNITPRICE,xs:anyType) *'.

If you have stuck with me through this long thing, I appreciate it! If you got this far here are my questions:

  1. What am I doing wrong with that xpath expression in the query?
  2. What can I do to speed up the import of the XML file into the column?
  3. What can I do to speed up the migration of the column into the table?
  4. Would it be better to make a bunch of tables for each UNIT instead of 1 table for all of them?
  5. Last, I hear rumors of people taking an XML object and parsing the a file say 10k rows at a time and looping through them in a procedure like this (import part of the file as an object variable, put it in the table, repeat on multiple rows and then migrate at 10k rows at a time also). Is this possible and would it help?
Dan
  • 167
  • 2
  • 9