Share via

Dealing with broken XSD

  • Article

I'm bringing this over to MSDN from its original home.

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="https://www.w3.org/2001/XMLSchema"')

insert @view (value) values (' targetNamespace="https://www.microsoft.com/mbs/crm/schemas/2004"')

insert @view (value) values (' xmlns:tns="https://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="https://www.w3.org/XML/1998/namespace"')

insert @view (value) values (' schemaLocation="https://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

Comments