Inside Microsoft CRM

6.17.2004

Dealing with broken XSD

Ok, I've been dealing with this broken XSD issue for so long now that I just can't stand it. The platform has ways to give you back XSD, but it's not really XSD, and it's not really friendly about doing so. Given the current v1.x API, which aren't exactly friendly to deal with, and their insistence on using strings of XML for everything, I put this script together to start hiding the complexities. Now, one thing you'll notice is that this script doesn't generate XSD in a flavor that the VS .NET XSD.EXE tool likes (it's not the greatest either, but it does work usually).

The cool thing is that there is a great tool available from the SD West Conference. This tool will happily eat the resulting XSD that this script generates and will create some very powerful client-side classes that should make everyone's life much easier. You'll still need to serialize the platform XML into a class, but that's pretty simple. The following C# snippit should do just fine (you'll have to grab some of the XML serialization references, IO, and Text, but that's left as an exercise for the reader).

The third code snip is the SQL script you've been waiting for. I recommend always generating the whole pile, you'll end up with about 3,000 lines of XSD and about 30,000 lines of C# when you're done, but it's worth it. Remember though, as shown, this script will not generate XSD that the XSD.EXE tool likes. Don't ask me why, it just doesn't (and that goes for generating typed DataSets too). There are ways to make it work, but would you want to when XSDObjectGen does all the right things in terms of creating real platform XML and dealing well with minimal update sets? Oh yeah, the classes work well with the ColumnSetXml parameter on Retrieve methods. I've even created XSD that represents collections of platform entities and serialized those properly.

As usual, nothing you've read here is remotely supported, and if you call support they'll likely have no idea what you're talking about. They might even ask why you're running SQL scripts in the metadata. I won't support this either. So don't ask. I'm making this available because it's something that needed to happen and never did. The bug with the bad XSD was found the day we RTM'd v1.0 and we never looked back (who'd ever want the schemas anyway, aren't XML strings self-describing...)


public static string ToString(object o)
{
StringBuilder sb = new StringBuilder();
XmlSerializer serializer = new XmlSerializer(o.GetType());
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();

ns.Add("", "");
TextWriter writer = new StringWriter(sb);
serializer.Serialize(writer, o, ns);

return sb.ToString();
}

public static object ToObject(Type t, string s)
{
XmlSerializer serializer = new XmlSerializer(t);
TextReader reader = new StringReader(s);

object o = null;
try {
o = serializer.Deserialize(reader);
} catch(Exception e) {
throw new Exception("Failed to convert XML to object", e);
}
return o;
}

void fooBar()
{
// create an account object in "client-space" and set some properties
Microsoft.Crm.WebServices.account a1 = new Microsoft.Crm.WebServices.account();
a1.accountcategorycode.Value = 6;
a1.accountcategorycode.name = "Corporate";

a1.creditlimit.Value = 123456.0F;
a1.creditlimit.value = "$123,456.00";

a1.creditonhold.Value = true;
a1.creditonhold.name = "Yes";

a1.createdon.type = 8;
a1.createdon.Value = userAuth.UserId;

a1.name = "This is account 1";
a1.description = "This is my sample account....";

// turn it into XML
string xml1 = ToString(a1);

Microsoft.Crm.Platform.Proxy.CRMAccount accountService =
new Microsoft.Crm.Platform.Proxy.CRMAccount();

// stuff in into the platform and get the new one back
string xml2 = accountService.CreateAndRetrieve(userAuth, xml1);

// turn the new one into an object
Microsoft.Crm.WebServices.account a2 =
(Microsoft.Crm.WebServices.account)ToObject(typeof(Microsoft.Crm.WebServices.account), xml2);



set nocount on

declare @view table (
idvalue int identity,
value nvarchar(4000)
)

declare @attributeName nvarchar(50)
declare @typeName nvarchar(50)
declare @entityName nvarchar(50)

declare @buildDate datetime
declare @buildNumber nvarchar(20)

select @buildDate = coalesce(BuildDate, getutcdate()),
@buildNumber = cast(coalesce(MajorVersion, 1) as nvarchar) + '.' + cast(coalesce(MinorVersion, 0) as nvarchar) + '.' + cast(coalesce(BuildNumber, 0) as nvarchar)
from BuildVersion

declare entityCursor cursor for
select LogicalName
from Entity
where IsIntersect = 0
and IsSecurityIntersect = 0
and IsLookupTable = 0
and IsAssignment = 0
and LogicalName not like '%activity%'
and LogicalName != 'activitypointer'
order by 1

-- write the top-level schema tags and namespace information
insert @view (value) values ('<?xml version="1.0" encoding="utf-8" ?>')

insert @view (value) values ('<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"')
insert @view (value) values (' targetNamespace="http://www.microsoft.com/mbs/crm/schemas/2004"')
insert @view (value) values (' xmlns:tns="http://www.microsoft.com/mbs/crm/schemas/2004"')

insert @view (value) values (' elementFormDefault="unqualified" ')
insert @view (value) values (' attributeFormDefault="unqualified" >')
insert @view (value) values ('')
insert @view (value) values (' <xsd:import namespace="http://www.w3.org/XML/1998/namespace"')
insert @view (value) values (' schemaLocation="http://www.w3.org/2001/xml.xsd" />')
insert @view (value) values ('')

insert @view (value) values ('')
insert @view (value) values (' <xsd:annotation>')
insert @view (value) values (' <xsd:documentation xml:lang="en">')
insert @view (value) values (' Copyright (c) ' + cast(year(getutcdate()) as nvarchar) + ' Microsoft Corp. All rights reserved.')
insert @view (value) values (' DO NOT EDIT - Schema automatically generated ')
insert @view (value) values (' Built on : ' + cast(@buildDate as nvarchar))
insert @view (value) values (' Version : ' + cast(@buildNumber as nvarchar))
insert @view (value) values (' </xsd:documentation>')
insert @view (value) values (' </xsd:annotation>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:simpleType name="uniqueidentifier">')
insert @view (value) values (' <xsd:restriction base="xsd:string">')
insert @view (value) values (' <xsd:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}" /> ')
insert @view (value) values (' </xsd:restriction>')
insert @view (value) values (' </xsd:simpleType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="keyType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:string">')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="principalType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:string">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')
insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')
insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="ownerType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:string">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')
insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')
insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="customerType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:string">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')
insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')
insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="lookupType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:string">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')
insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')
insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="picklistType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:int">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="booleanType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:boolean">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="moneyType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:float">')
insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="numberType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:int">')
insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="decimalType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:float">')
insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="floatType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:float">')
insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="dateTimeType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:dateTime">')
insert @view (value) values (' <xsd:attribute name="date" type="xsd:string"/>')
insert @view (value) values (' <xsd:attribute name="time" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="statusType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:int">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

insert @view (value) values (' <xsd:complexType name="stateType">')
insert @view (value) values (' <xsd:simpleContent>')
insert @view (value) values (' <xsd:extension base="xsd:int">')
insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')
insert @view (value) values (' </xsd:extension>')
insert @view (value) values (' </xsd:simpleContent>')
insert @view (value) values (' </xsd:complexType>')
insert @view (value) values ('')

-- open the cursor
open entityCursor
fetch entityCursor into @entityName

while @@fetch_status = 0
begin

insert @view (value) values (' <xsd:complexType name="' + @entityName + '">')
insert @view (value) values (' <xsd:sequence>')

declare attributeCursor cursor for
select Attribute.LogicalName,
case
when AttributeTypes.XmlType in ('dateTime.tz', 'datetime') then 'tns:dateTimeType'
when AttributeTypes.XmlType = 'Boolean' then 'tns:booleanType'

when AttributeTypes.XmlType = 'picklist' then 'tns:picklistType'
when AttributeTypes.XmlType = 'state' then 'tns:stateType'
when AttributeTypes.XmlType = 'status' then 'tns:statusType'

when AttributeTypes.XmlType = 'primarykey' then 'tns:keyType'
when AttributeTypes.XmlType = 'customer' then 'tns:customerType'
when AttributeTypes.XmlType = 'lookup' then 'tns:lookupType'
when AttributeTypes.XmlType = 'owner' then 'tns:ownerType'

when AttributeTypes.XmlType = 'uuid' then 'tns:keyType'

when AttributeTypes.XmlType = 'timezone' then 'xsd:int'
when AttributeTypes.XmlType in ('integer', 'int', 'bigint', 'smallint', 'tinyint') then 'tns:numberType'
when AttributeTypes.Description = 'money' then 'tns:moneyType'
when AttributeTypes.Description = 'decimal' then 'tns:decimalType'
when AttributeTypes.Description = 'float' then 'tns:floatType'

else 'xsd:' + AttributeTypes.XmlType
end
from Entity join Attribute on (Entity.EntityId = Attribute.EntityId)
join AttributeTypes on (Attribute.AttributeTypeId = AttributeTypes.AttributeTypeId)
where Entity.LogicalName = @entityName
and (Attribute.ValidForReadAPI = 1 or Attribute.ValidForUpdateAPI = 1 or Attribute.ValidForCreateAPI = 1)
and Attribute.AttributeOf is NULL
and Attribute.AggregateOf is NULL
order by Attribute.LogicalName

open attributeCursor
fetch attributeCursor into @attributeName, @typeName

while @@fetch_status = 0
begin
insert @view (value) values (' <xsd:element name="' + @attributeName + '" type="' + @typeName + '" />')
fetch attributeCursor into @attributeName, @typeName
end

close attributeCursor
deallocate attributeCursor

insert @view (value) values (' </xsd:sequence>')
insert @view (value) values (' </xsd:complexType>')

fetch entityCursor into @entityName

if @@fetch_status = 0
begin
insert @view (value) values ('')
end
end

close entityCursor
deallocate entityCursor

insert @view (value) values ('</xsd:schema>')

select value
from @view order by idvalue


Bonus if you've read this far. To get collection classes too, add some more SQL like this before the second fetch, and make sure you add a column for the collection name.


if @collectionname is not null
begin
insert @view (value) values ('')
insert @view (value) values (' ')
insert @view (value) values (' ')
insert @view (value) values (' ')
insert @view (value) values ('
')
insert @view (value) values ('
')
end