Error While Creating Schema from XSD file and Validating it

Question

This is my Xml file

<Test>
 <Detail>
  <name id="123" age="1">Abc</name>
  <lastname>cd</lastname>
 </Detail>
</Test>

And this is my Corresponding XSD file Which Can be generated using http://xmlgrid.net/xml2xsd.html

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
          <!-- XML Schema Generated from XML Document on Thu Jun 08 2017 22:08:05 GMT+0530 (India Standard Time) -->
          <!-- with XmlGrid.net Free Online Service http://xmlgrid.net -->
       <xs:element name="Test">
              <xs:complexType>
                     <xs:sequence>
                            <xs:element name="Detail">
                                   <xs:complexType>
                                          <xs:sequence>
                                                 <xs:element name="name" type="xs:string">
                                                        <xs:complexType>
                                                               <xs:attribute name="id" type="xs:int"></xs:attribute>
                                                               <xs:attribute name="age" type="xs:int"></xs:attribute>
                                                           </xs:complexType>
                                                    </xs:element>
                                                 <xs:element name="lastname" type="xs:string"></xs:element>
                                             </xs:sequence>
                                      </xs:complexType>
                               </xs:element>
                        </xs:sequence>
                 </xs:complexType>
          </xs:element>
   </xs:schema>

When i tried to create a Schema in SQL Server

CREATE XML SCHEMA COLLECTION TestSchema 
AS'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
          <!-- XML Schema Generated from XML Document on Thu Jun 08 2017 22:08:05 GMT+0530 (India Standard Time) -->
          <!-- with XmlGrid.net Free Online Service http://xmlgrid.net -->
       <xs:element name="Test">
              <xs:complexType>
                     <xs:sequence>
                            <xs:element name="Detail">
                                   <xs:complexType>
                                          <xs:sequence>
                                                 <xs:element name="name" type="xs:string">
                                                        <xs:complexType>
                                                               <xs:attribute name="id" type="xs:int"></xs:attribute>
                                                               <xs:attribute name="age" type="xs:int"></xs:attribute>
                                                           </xs:complexType>
                                                    </xs:element>
                                                 <xs:element name="lastname" type="xs:string"></xs:element>
                                             </xs:sequence>
                                      </xs:complexType>
                               </xs:element>
                        </xs:sequence>
                 </xs:complexType>
          </xs:element>
   </xs:schema>'

I get the error as

Msg 2305, Level 16, State 1, Line 1
Element or attribute type specified more than once. Location: '/*:schema[1]/*:element[1]/*:
complexType[1]/*:sequence[1]/*:element[1]/*:
complexType[1]/*:sequence[1]/*:element[1]/*:
complexType[1]'.

When i removed the type="xs:string" in <xs:element name="name" type="xs:string"> I can create my schema successfully without the error.

But while validating my Schema i got the error as

declare @x XML(TestSchema)

begin try
select @x='<?xml version="1.0" encoding="utf-8"?>
<Test>
<Detail>
<name id="123" age="1">Abc</name>
<lastname>cd</lastname>
</Detail>
</Test>'
end try
begin catch
print Error_Message()
end catch

Can Any body help me to solve this ?

Thanks in advance,Jayendran

Answer 1

In XSD, you can use xs:complexType and xs:simpleContent in order to allow both attributes and content on the same element:

<xs:element name="name">
    <xs:complexType>
        <xs:simpleContent>
            <xs:extension base="xs:string">
                <xs:attribute name="id" type="xs:int"></xs:attribute>
                <xs:attribute name="age" type="xs:int"></xs:attribute>
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>
</xs:element>