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:
- What am I doing wrong with that xpath expression in the query?
- What can I do to speed up the import of the XML file into the column?
- What can I do to speed up the migration of the column into the table?
- Would it be better to make a bunch of tables for each UNIT instead of 1 table for all of them?
- 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?