Problem loading XML Files

I have been trying unsuccessfully to load a directory full of XML files into SQL Server 2005 Standard. I have not been able to make it work, and I would be grateful for any advice.

A simple version of the files that I am trying to load is TEST.NWS just below. I have removed almost all of the content from the files, leaving as a file TEST.NWS. (Hopefully I have not cut out too much.) This version of the test file leaves me with the same error messages I have with the full file. I have a directory with many such files and each file is much too big to edit manually.

Start TEST.NWS--

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC001">

</NewsComponent>

</NewsML>

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC002">

</NewsComponent>

</NewsML>

END-

The schema I am using is here: http://www.newsml.org/dl.php?fn=NewsML/1.2/specification/NewsML_1.2.xsd

I also tried this schema: http://www.nitf.org/IPTC/NITF/3.3/specification/schema/nitf-3-3.xsd

One version of the steps that I follow are here: http://msdn2.microsoft.com/en-us/library/ms139886.aspx At stage 8 of the "how to" recipe, when I click on the Columns, I get an error. The wording of the error depends on which of the two schemas I use. If I instead click next I also get an error. Clearly I am doing something wrong. Any suggestion for how to fix this will be most welcome. Thanks.

mzfrank,

Please you please copy and paste the error messages you are getting and optional the T-SQL script you are running?

Thanks,

Galex

|||

I was not using a script. I was following the MSDN "How to: Extract Data Using the XML Source" it uses SSIS rather than a T-SQL script. (Is this a task better done using T-SQL rather than SSIS?)

If I use NewsML_1.2.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared.

If I use nitf-3-3.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: The XML Source Adapter does not support mixed content model on Complex Types.

If I ask it to infer a schem I get:

Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

|||

I am having the same issue. I posted in the SSIS forum, but this is what I wrote there:

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>