SQL -> XML (XSD)

Question

SQL -> XML (XSD)

david.alcock

SSCertifiable

Points: 6021
More actions
February 14, 2012 at 3:55 am

#255549

Just need pointing in the right direction really. I am designing a SSIS package to basically load some documents into SQL and output to XML which I am fairly familiar with.
However, one part that I am stuck on is how to validate a SQL output to XML against an XSD file?
Edit: This is where I am heading, please advise if I am barking up the wrong tree.
I am planning to use the XML SCHEMA COLLECTION effectively copying the xml in there. Can I use this collection to validate a SELECT FOR XML statement?
'Only he who wanders finds new paths'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I think it's pretty much a case of declaring an XML variable using your SCHEMA COLLECTION, then assigning the result of your FOR XML to that variable.

This will then error if it does not match the XSD. I'll see if I can knock up a sample...

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Sample below:

CREATE XML SCHEMA COLLECTION TestSchema

AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="foobar">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="foo"/>

<xsd:element name="bar"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>'

DECLARE @GoodXML XML(TestSchema)='<foobar>

<foo>A</foo>

<bar>B</bar>

</foobar>'

go

--missing bar element

DECLARE @BadXML XML(TestSchema)='<foobar>

<foo>A</foo>

</foobar>'

So, you should just take your for XML output and try to assign it to a variable declared as that SCHEMA COLLECTION

david.alcock SSCertifiable Points: 6021 More actions · Answer 3

Thanks for the reply, thats what I am looking at admittedly I have never ventured into XML in SQL before bar a coupel of simpel queries.

I have the schema collection created and a table created from it with a XML type column, my idea was to create a SELECT FOR XML on the source table and load/insert into this column, presumably if the insert fails then it has failed validation?

'Only he who wanders finds new paths'

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Yep, that's the whole point of the schema collection. An insert will fail (with a specific XSD validation error) if you try to insert malformed documents

david.alcock SSCertifiable Points: 6021 More actions · Answer 5

Brilliant, that is exactly what I need to do. Thank you very much for clearing that up, pleased I was on the right track!

'Only he who wanders finds new paths'