Clever Geek Handbook

XML validation in SQL Server

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 xsd for it and create schema in SQL Server to check:

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 check the xml against the schema, I have the following error:

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

I saw something similar here, but I tried and didn't solve the problem. Can anyone please help? Thank.

+3


source to share


1 answer


Seems to work well here. I am using SQL Server 2012.

Doing the following will validate your XML against dbo.MBS_IMPORT_SCHEMA. Make sure you have GO separator between XML Schema collection creation and 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>'
+2


source






More articles:


All Articles