How to make a Custom Data Generator for SQL XML DataType.
- by Keith Sirmons
Howdy,
I am using Visual Studio 2010 and am playing around with the Database Projects.
I am creating a DataGenerationPlan to insert data into a simple table, in which one of the column datatypes is XML.
Out of the box, the generation plan uses the Regular Expression generator and generates something like this : HGcSv9wa7yM44T9x5oFT4pmBkEmv62lJ7OyAmCnL6yqXC2X..........
I am looking at creating a custom data Generator for this data type and have followed this site for the basics: http://msdn.microsoft.com/en-us/library/aa833244.aspx
This example works if I am creating a string datatype and using it for a nvarchar datatype.
What do I need to change to hook this Generator to the XML Datatype?
Below are my code files. The string property works for nvarchar. The XElement property does not work for the xml datatype, and the RecordXMLDataGenerator is not listed as an option in the Generator column for the generation plan.
CustomDataGenerators:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.Tools.DataGenerator;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.Sql;
using System.Xml.Linq;
namespace CustomDataGenerators
{
[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
public class RecordXMLDataGenerator : Generator
{
private XElement _RecordData;
[Output(Description = "Generates string of XML Data for the Record.", Name = "RecordDataString")]
public string RecordDataString
{
get { return _RecordData.ToString(SaveOptions.None); }
}
[Output(Description = "Generates XML Data for the Record.", Name = "RecordData")]
public XElement RecordData
{
get { return _RecordData; }
}
protected override void OnGenerateNextValues()
{
base.OnGenerateNextValues();
XElement element = new XElement("Root",
new XElement("Children1", 1),
new XElement("Children6", 6)
);
_RecordData = element;
}
}
}
XML Extensions File:
<?xml version="1.0" encoding="utf-8" ?>
<extensions assembly="" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">
<extension type="CustomDataGenerators.RecordXMLDataGenerator" assembly="CustomDataGenerators, Version=1.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxx" enabled="true"/>
</extensions>
Table.sql:
CREATE TABLE [dbo].[Record]
(
id int IDENTITY (1,1) NOT NULL,
recordData xml NULL,
userId int NULL,
test nvarchar(max) NULL,
rowver rowversion NULL,
CONSTRAINT pk_RecordID PRIMARY KEY (id)
)
