-1

as the title sugests i have to import large (6GB) XML files into an SQLExpress server. Since this has to happen on a daily basis on the VServer itself and im not allowed to install more Software i'm limited to powershell (although upgraded to Version 4). My source of inspiration has been Use PowerShell to Load XML Data into SQL Server

My current script looks like this:

#database variables
$serverInstance="QLIKVIEWSQL\QLIKV_SQLEXPRESS";
$database = "Xml-Import_TEST"; 
$table = "dbo.vkjournal";

#opening the sql connection
$cn = new-object System.Data.SqlClient.SqlConnection;
$user="user";
$password="password"
$cn.ConnectionString="Server=$serverInstance;Database=$database;user   Id=$user;password=$password;Integrated Security=SSPI;";
$cn.Open();


#Semi-automatic creation of the XmlSchema
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT * FROM "+$table+" WHERE 1 = 2";
$cmd.Connection = $cn
$da = New-Object System.Data.SqlClient.SqlDataAdapter;
$da.SelectCommand = $cmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds, $table) | Out-Null
$ds.WriteXmlSchema('C:\Users\qlikview\Desktop\SQLTEST\'+$table+'.xsd');


#Reading the xml-schema and loading bw1.xml into an DataSet object. Then bulkcopying to sql.
$ds = new-object "System.Data.DataSet"
$ds.ReadXmlSchema("C:\Users\qlikview\Desktop\SQLTEST\dbo.vkjournal.xsd");
$ds.ReadXml("C:\Users\qlikview\Desktop\SQLTEST\bw1.xml")
$dtProd = $ds.Tables[0]
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = $table;
$bc.WriteToServer($dtProd)

$cn.Close()

As usual powershell doesnt throw any errors, just the words IgnoreSchema (if i comment out the ReadXmlSchema line) or InferSchema (if i dont) and nothing happens on the SQLServer side.

The created XMLSchema(shortened) looks like this:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="dbo.vkjournal">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="KASS_NR" type="xs:short" minOccurs="0" />
              <xs:element name="VKJO_DATUM" type="xs:dateTime" minOccurs="0" />
              <xs:element name="VKJO_ZEIT" type="xs:duration" minOccurs="0" />
              <xs:element name="VKJO_BESTE" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

And although i have tried to change some element names as suggested in 1 the results havent changed.

I would be very grateful for some hints, thanks.

hellfiend
  • 108
  • 9
  • One thing that concerns me Express version size limit is 10GB, 2x load and you are done. – Lukasz Szozda Aug 31 '15 at 14:03
  • 6GB of XML is maybe (guess, probably even less) 1GB of SQLdata, and the data are getting Overwritten. After the Import works, i hav a good explanation why we need a full version or why to install MySQL. – hellfiend Aug 31 '15 at 14:11
  • Why Powershell, use T-SQL directly, `DECLARE @messagebody XML; SELECT @messagebody = BulkColumn FROM OPENROWSET(BULK 'C:\Work\Products1000.XML', SINGLE_CLOB) AS X; INSERT INTO table(xml_column) SELECT @messagebody` – Lukasz Szozda Aug 31 '15 at 15:40
  • Honestly because my Powershell is better than my T-SQL :) I tried your propose and got an *Column name or number of supplied values does not match table definition* error. My guess is that the xml values and the table values are different? – hellfiend Sep 01 '15 at 08:07
  • Do you store XML File itself in database or do you want to parse XML and put values from XML in tables in database? – Lukasz Szozda Sep 01 '15 at 08:14
  • Put the values from XML in the table – hellfiend Sep 01 '15 at 08:43

1 Answers1

0

Even if the Question was somehow not popular, i found a working method using this microsoft link. The full script throw some errors so i ditched the validation and copied only the bulk load. I had to install msxml 6.0 and i did install SQLXML 4.0 if thats important. For those who have big table definitions like i did, the script above (see section #Semi-automatic creation of the XmlSchema) helps tremendously reducing the needed hand writting.

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=SERVER\SQLEXPRESS_INSTANCE;database=YOURDB;integrated security=SSPI"
objBL.ErrorLogFile = "C:\error.log"

' Check constraints and initiate transaction (if needed)
   ' objBL.CheckConstraints = True
   ' objBL.Transaction=True
  'Execute XML bulkload using file.
  objBL.Execute "C:\XSDfile.xml", "C:\File.xml"
  set objBL=Nothing
hellfiend
  • 108
  • 9