Can I validate this xml somehow?

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.
>

Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.