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>'
*/
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>'
> */
>
|||Brilliant! Excellent. Thanks.
"Michael Rys [MSFT]" <mrys@.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[vbcol=seagreen]
> you probably could write it more efficiently.
> HTH
> Michael
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:uGyjKlx3EHA.3504@.TK2MSFTNGP12.phx.gbl...
schema.[vbcol=seagreen]
attribute.
>
