SQLXML Bulk Load - Generate Guids

Question

I am following the SQLXML 4.0 documentation to produce an annotated XSD so that I can produce and consume XML to/from the SQL Server.  I am experimenting with the bulk load process now as described at : http://msdn.microsoft.com/en-us/library/ms171878.aspx

My first test is failing miserably.  A simple insert to a single table where the primary key is a uniqueidentifier.  Infact nearly all the primary keys in my system are uniqueidentifiers.

The bulk load only works when I specify a value for the primary key in the XML.  When it is not specified, an error "No data was provided for column '<column name>' for table '<table name>', and this column cannot contain NULL values.

I have annotated the uniqueidentifier column in the XSD as:

<xsd:element name="Guid" sql:datatype="uniqueidentifier" sql:guid="generate" type="xsd:string" />

This seems crazy to me that Guid generation is not supported with XMLBulk Load.  I can't expect the sender to include Guids in the xml.

Any help would be most appreciated.

Answer 1

Hi aihuddymsdn,

Please have a look at the following thread:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/16a95466-406c-49e1-ac90-8c3ceb903523

As it indicates, we have to provide the GUID values in the XML file. The GUID values cannot be generated automatically during the bulk load process.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

I had a look at this and I don't think SQLXML Bulkload supports foreign key generation for UNIQUEIDENTIFIER datatype (basically the .KeepIdentity method is for IDENTITY columns only), however I did code a workaround.  Basically you can have an additional column in the parent and child tables ( guidParentId ) in my example, and populate it afterwards, eg

Sample XSD

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:annotation>
    <xs:appinfo>
      <sql:relationship name="parentChild"
            parent="parent"
            parent-key="parentId"
            child="child"
            child-key="parentId" />
    </xs:appinfo>
  </xs:annotation>

    <xs:element name="root" sql:is-constant="1">
      <xs:complexType>
        <xs:sequence>
          <xs:element maxOccurs="unbounded" name="parent">
            <xs:complexType>
              <xs:sequence>
                <xs:element maxOccurs="unbounded" name="child" sql:relationship="parentChild">
                  <xs:complexType>
                    <xs:attribute name="name" type="xs:string" use="required" />
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
              <xs:attribute name="name" type="xs:string" use="required" />
            </xs:complexType>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
  
</xs:schema>

Sample XML

<root>

  <parent name="parent 1">
    <child name="child 1" />
    <child name="child 2" />
  </parent>

  <parent name="parent 2">
    <child name="child 3" />
    <child name="child 4" />
  </parent>

</root>

Sample schema

USE tempdb
GO

IF OBJECT_ID('tempdb..child') IS NOT NULL DROP TABLE child
IF OBJECT_ID('tempdb..parent') IS NOT NULL DROP TABLE parent
GO

CREATE TABLE parent (
	parentId		INT IDENTITY UNIQUE,
	name			VARCHAR(20),
	guidParentId	UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY
	)
GO

CREATE TABLE child (
	childId			INT IDENTITY PRIMARY KEY,
	parentId		INT NOT NULL FOREIGN KEY REFERENCES parent(parentId),
	name			VARCHAR(20),
	guidParentId	UNIQUEIDENTIFIER NULL
	)
GO

Final UPDATE

UPDATE c
SET c.guidParentId = p.guidParentId
FROM parent p 
	INNER JOIN child c ON p.parentId = c.parentId

I realise this makes your import a two-step process but this may be an acceptable workaround.