Validate xml in SQL Server

Question

I have the following xml:

<?xml version="1.0" encoding="windows-1252"?>
<!--MBS Data - Created 03/06/2014  11:40:13-->
<MBS_XML>
  <Data>
    <ItemNum>3</ItemNum>
    <SubItemNum>1</SubItemNum>
  </Data>
  <Data>
    <ItemNum>4</ItemNum>
    <SubItemNum>2</SubItemNum>
  </Data>
  <Data>
    <ItemNum>20</ItemNum>
    <SubItemNum>3</SubItemNum>
  </Data>
  <Data>
    <ItemNum>23</ItemNum>
    <SubItemNum>1</SubItemNum>
  </Data>
</MBS_XML>

I am using cmd to generate the xsd for it and create schema in SQL Server to validate:

CREATE XML SCHEMA COLLECTION dbo.MBS_IMPORT_SCHEMA
AS'
  <xs:schema id="MBS_XML" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="MBS_XML" msdata:IsDataSet="true" msdata:Locale="en-US">
  <xs:complexType>
  <xs:choice minOccurs="0" maxOccurs="unbounded">
  <xs:element name="Data">
  <xs:complexType>
  <xs:sequence>
  <xs:element name="ItemNum" type="xs:string" minOccurs="0" /> 
  <xs:element name="SubItemNum" type="xs:string" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
  ';

But when I validate the xml against the schema I have the following error:

XML Validation: Declaration not found for element 'MBS_XML'. Location: /*:MBS_XML[1]

I have seen something similar here but I tried and did not resolve my issue. Could someone help please? Thanks.

Svein Fidjestøl · Accepted Answer · 2014-11-03 22:00:20Z

1

Seems to work fine here. I'm using SQL Server 2012.

Executing the following will validate your XML against the dbo.MBS_IMPORT_SCHEMA. Make sure you have the GO separator in between the XML Schema Collection creation and the XML validation

CREATE XML SCHEMA COLLECTION dbo.MBS_IMPORT_SCHEMA
AS'
  <xs:schema id="MBS_XML" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="MBS_XML" msdata:IsDataSet="true" msdata:Locale="en-US">
  <xs:complexType>
  <xs:choice minOccurs="0" maxOccurs="unbounded">
  <xs:element name="Data">
  <xs:complexType>
  <xs:sequence>
  <xs:element name="ItemNum" type="xs:string" minOccurs="0" /> 
  <xs:element name="SubItemNum" type="xs:string" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
  ';

GO

DECLARE @x XML(dbo.MBS_IMPORT_SCHEMA)
SELECT @x = '<?xml version="1.0" encoding="windows-1252"?>
<!--MBS Data - Created 03/06/2014  11:40:13-->
<MBS_XML>
  <Data>
    <ItemNum>3</ItemNum>
    <SubItemNum>1</SubItemNum>
  </Data>
  <Data>
    <ItemNum>4</ItemNum>
    <SubItemNum>2</SubItemNum>
  </Data>
  <Data>
    <ItemNum>20</ItemNum>
    <SubItemNum>3</SubItemNum>
  </Data>
  <Data>
    <ItemNum>23</ItemNum>
    <SubItemNum>1</SubItemNum>
  </Data>
</MBS_XML>'

answered Nov 3, 2014 at 22:00

Svein Fidjestøl

3,2063 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Blue Bamboo Over a year ago

That was quick. You were right. I validated against the wrong schema name! Thank you very much for verifying it for me.

Collectives™ on Stack Overflow

Validate xml in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related