"Jeremy Chapman" <NoSpam@Please.com> wrote in message
news:uGyjKlx3EHA.3504@TK2MSFTNGP12.phx.gbl...
>I have a stored procedure which takes a varchar.  the contents of the
> varchar *Should* be a valid xml and should represent a pre-defined schema.
> How can I validate that the xml is a valid schema?
>
> I need to ensure that the root node is <s>, that it can have many <p>
> nodes
> but no other nodes, and each <p> node must have a 'n' and a 'v' attribute.
>
> My test stored proc is as follows:
>
> CREATE PROCEDURE dbo.pXMLTest
> @strXML varchar(1024)
> AS
> DECLARE @idoc int
>
> EXECUTE sp_xml_preparedocument @idoc OUTPUT, @strXML
>
> --Must validate that the schema is correct
>
> SELECT
> n,v
> FROM
> OpenXML(@idoc,'/s/p')
> WITH
> (n varchar(1024),
>         v varchar(1024))
>
> EXECUTE sp_xml_removedocument @iDoc
>
> GO
>
> /* Test code
>
> --valid
> pXMLTest '<s>
>  <p n="param1" v="value1"/>
>  <p n="param2" v="value2"/>
>   </s>'
>
> --invalid, stored proc should return an error
>
> pXMLTest '<s>
>  <x n="param1" v="value1"/>
>  <p n="param2" v="value2"/>
>   </s>'
> */
>
>

"Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message
news:O#RcS813EHA.1596@tk2msftngp13.phx.gbl...
> You do not have a schema validator in SQL Server 2000. You would have to
> validate on the client/midtier using either MSXML (native code) or
> System.XML (.Net Framework) if you have an XML schema.
>
> Inside the database, you can do some query based validation (see below for
> an example). However, that is quite expensive compare to using a schema
and
> a validator.
>
> So, to validate that the top-level node is s that can only have p elements
> as child nodes and p nodes must have an n and v attribute, we can make use
> of the node table of the XML structure. Since we need to self-join/access
> the structure several times, we use a temp table to store the data. You
can
> use the following statements to help build your stored proc:
>
> -- The following are some test cases
> --N'<s><p a="a1" n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<r><p n="n1" v="v1"/><p n="n2" v="v2"/></r>'
>
> --N'<s><p a="a1" n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<s><q a="a1" n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<s><p n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<s><p v="v1"/><p n="n2"/></s>'
>
> --N'<s>text<p n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<s><?pi?><p n="n1" v="v1"/><p n="n2" v="v2"/></s>'
>
> --N'<s><x n="param1" v="value1"/><p n="param2" v="value2"/></s>'
>
> --N'<s/>'
>
> --N'<s><p/><p/></s>'
>
> --N'<s><p><n>n1</n><v>v1</v></p><p n="n2" v="v2"/></s>'
>
> --N'<s><p n="n1" v="v1">a</p><p n="n2" v="v2"/></s>'
>
> SELECT * INTO #T FROM OpenXML(@idoc, '/s') -- Make sure that we have an s
> element root
>
> -- The following returns pass if the conditions hold.
>
> -- Note that the root element always has id 0 and has always parentid NULL
> which simplifies some of the subselects
>
> SELECT CASE WHEN count(*)= 0 THEN 'fail' ELSE 'pass' END
>
> from #T as T
>
> WHERE -- Check for p elements
>
> T.localname = N'p' and T.namespaceuri IS NULL AND T.nodetype=1
>
> AND -- that have an s parent that is the root element (note there cannot
be
> another root)
>
> T.parentid in
>
> (SELECT id from #T as Tp WHERE
>
> Tp.localname = N's' and Tp.namespaceuri IS NULL and Tp.parentid IS NULL)
>
> AND -- no other nodes under the root element exists other than p elements
>
> NOT EXISTS(
>
> SELECT id from #T as Ts WHERE
>
> Ts.parentid = 0 and
>
> (Ts.localname<>N'p' or NOT(Ts.namespaceuri is null or Ts.nodetype<>1) ))
>
> AND -- all p elements have attributes
>
> EXISTS(
>
> SELECT id from #T as Ta
>
> WHERE Ta.parentid = T.id and Ta.nodetype = 2
>
> )
>
> AND -- all p elements have both v and n attributes
>
> 2= ALL (
>
> SELECT count(id)
>
> from #T Ta
>
> WHERE Ta.parentid IN (SELECT Ts.id FROM #T as Ts WHERE Ts.parentid = 0)
>
> AND Ta.nodetype = 2 AND
>
> (namespaceuri IS NULL AND localname = N'v'
>
> OR namespaceuri IS NULL AND localname = N'n')
>
> group by Ta.parentid
>
> )
>
> AND -- none of the p elements have non-v or n attributes or non attribute
> children
>
> 0 = ALL(
>
> SELECT count(id)
>
> from #T Ta
>
> WHERE Ta.parentid IN (SELECT Ts.id FROM #T as Ts WHERE Ts.parentid = 0)
>
> AND (NOT(
>
> Ta.namespaceuri IS NULL AND Ta.localname = N'v'
>
> OR Ta.namespaceuri IS NULL AND Ta.localname = N'n'
>
> ) OR Ta.nodetype <> 2))
>
> drop table #T
>
> I have tested the statement with the examples above and am almost sure
that
> you probably could write it more efficiently.
>
> HTH
> Michael
>
> "Jeremy Chapman" <NoSpam@Please.com> wrote in message
> news:uGyjKlx3EHA.3504@TK2MSFTNGP12.phx.gbl...
> >I have a stored procedure which takes a varchar.  the contents of the
> > varchar *Should* be a valid xml and should represent a pre-defined
schema.
> > How can I validate that the xml is a valid schema?
> >
> > I need to ensure that the root node is <s>, that it can have many <p>
> > nodes
> > but no other nodes, and each <p> node must have a 'n' and a 'v'
attribute.
> >
> > My test stored proc is as follows:
> >
> > CREATE PROCEDURE dbo.pXMLTest
> > @strXML varchar(1024)
> > AS
> > DECLARE @idoc int
> >
> > EXECUTE sp_xml_preparedocument @idoc OUTPUT, @strXML
> >
> > --Must validate that the schema is correct
> >
> > SELECT
> > n,v
> > FROM
> > OpenXML(@idoc,'/s/p')
> > WITH
> > (n varchar(1024),
> >         v varchar(1024))
> >
> > EXECUTE sp_xml_removedocument @iDoc
> >
> > GO
> >
> > /* Test code
> >
> > --valid
> > pXMLTest '<s>
> >  <p n="param1" v="value1"/>
> >  <p n="param2" v="value2"/>
> >   </s>'
> >
> > --invalid, stored proc should return an error
> >
> > pXMLTest '<s>
> >  <x n="param1" v="value1"/>
> >  <p n="param2" v="value2"/>
> >   </s>'
> > */
> >
> >
>
>