SQLXMLBulkLoad with delimited values

Question

Hi,

I have tried the SQLXMLBulkLoad 4.0 to load the data in the XML format to the Database using XSD.
While I do the implementation, I have faced the problem in mapping the delimited values.

Here is the details,

XML Data file
-------------
<Root>
<Sample>x1:y1:z1|x2:y2:z2</Sample>
</Root>

Note:
Row delimiter is "|"
colum delimiter is ":"

XSD
---
<xsd:schema xmlns:schema="Root" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="Root" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="TEST">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ID" type="sqltypes:int" />
        <xsd:element name="VALUE">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="10" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="TYPE">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
              <xsd:maxLength value="10" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

I need to split the data and inserted into "Test" table as follows

ID Value Type
X1    y1      Z1
X2    y2      Z2

OR

I need to run the stored procedure in SQLXML XSD and pass the parameter values as "x1:y1:z1|x2:y2:z2"

Can anyone help me on this problem.

Thanks,
Gopimanikandan

Answer 1

If your XML look like this then this would be really easy:

<Root>
	<row id="x1" value="y1" type="z1" />
	<row id="x2" value="y2" type="z2" />
<Root>

Your actual XML looks very strange to me - you are basically using two types of array: xml and delimited list.  I don't think you should mix two types of array as you have to shred them both.

If you can't change your XML, then you will have to load it to a staging table then shred it, eg

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<xs:element name="Root">
		<xs:complexType>
			<xs:sequence>
				<xs:element name="Sample" type="xs:string" />
			</xs:sequence>
		</xs:complexType>
	</xs:element>
</xs:schema>

See here for a string splitting function.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Answer 2

Thanks for your response.

is it possible to call the stored procedure and pass the parameter as the delimited value?

Thanks,

Gopimanikandan

Answer 3

Well yes but that sounds like a row-processing approach to me.  You would be better off using a set-based approach, eg

• load the data to a staging table ( either using XML bulkload or OPENXML, example below )
• split into rows by '|'
• split into columns by ':'

Simple example below.  For large volumes of data I'm not sure this will scale very well so you might need to break it up.

CREATE TABLE #staging ( yourXML XML )

INSERT INTO #staging ( yourXML )
SELECT *
FROM OPENROWSET( BULK 'c:\temp\temp.xml', SINGLE_CLOB ) x(y)
GO

-- Split by |
SELECT *
FROM
	(
	SELECT itemNumber, item
	FROM
		(
		SELECT x.c.value('.', 'NVARCHAR(MAX)') xValue
		FROM #staging s
			CROSS APPLY s.yourXML.nodes('Root/Sample' ) x(c)
		)	y
		CROSS APPLY dbo.DelimitedSplitN4K ( y.xValue, '|' )
	) z
	CROSS APPLY dbo.DelimitedSplitN4K ( z.item, ':' )
GO

DROP TABLE #staging

Answer 4

Hi wBob,

Finally I got imported the XML file into the database server. But Now I  faced the performance problem.

I have to loaded the data in the indexed table(Unique and Non cluster index), that time It was very slow for about 1 million items

Here is the code

------------------------

               SQLXMLBulkLoad4Class objBL = new SQLXMLBulkLoad4Class();
                objBL.ConnectionString =ConnectionString;
                objBL.IgnoreDuplicateKeys = true;
                objBL.ErrorLogFile = errorlogPath + "error.xml";
                objBL.CheckConstraints = false;
                objBL.Transaction = true;
                objBL.Execute(schemaPath, filePath);

Thanks,

Gopimanikandan

Answer 5

Try loading into a table with no indexes and applying the indexes afterwards, see if that makes a difference.

What version of SQL Server are you using?

Answer 6

I am using SQL Server 2008