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 Merge a table with itself ?

Hi,
I have a table which contains several integer columns that represent
similiar date related information. ie
RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
I need a view of the table where these different columns have been merged
into a single column, so where there was one row with x number of integers,
I would have X number of rows with a single integer. Is this possible ?
What I am actually wanting to do is sort the rows based on the value that is
highest / lowest from the values in the row.
Can this be done ?
Thanks,
Jon.Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:
> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>|||Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote:
> > Hi,
> >
> > I have a table which contains several integer columns that represent
> > similiar date related information. ie
> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> >
> > I need a view of the table where these different columns have been merged
> > into a single column, so where there was one row with x number of integers,
> > I would have X number of rows with a single integer. Is this possible ?
> >
> > What I am actually wanting to do is sort the rows based on the value that is
> > highest / lowest from the values in the row.
> >
> > Can this be done ?
> >
> > Thanks,
> >
> > Jon.
> >
> >
> >|||Alejandro,
Thanks for these responses.
I like the unpivot one particuarly. I am just seeing if I can get it to work
now !!
I will let you know.
Thanks again,
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
> Bishman,
> If you are working with 2005, then you can also use new operator
> "unpivot".
> create table dbo.t1 (
> a int not null identity unique,
> b int,
> c int,
> d int
> )
> go
> insert into dbo.t1(b, c, d) values(10, 20, 30)
> insert into dbo.t1(b, c, d) values(40, 50, 60)
> go
> select
> a, val
> from
> (
> select a, b as c1, c as c2, d as c3
> from dbo.t1
> ) as pvt
> unpivot
> (val for c in (c1, c2, c3)) as unpvt
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >|||This is working well except I am trying to go past 4 columns ( I need 6)
I get this error when I go to 5 columns....
The type of column "cole" conflicts with the type of other columns specified
in the UNPIVOT list.
with this SQL
USE CastleProspect_Dev
select
ExtractNumber,rowID, RenewalDate
from
(select ExtractNumber,rowID,HomeRenewalMonthDay as a,ContentsRenewalMonthDay
as b,MotorRenewalMonthDay as c, BreakdownRenewalMonthDay as d ,
PetRenewalMonthDay as e from dbo.ExtractDetail) as pvt
unpivot
(RenewalDate for aa in (a, b, c, d, e)) as unpvt
where extractnumber = 99
go
Any Ideas '
Thanks again. This looks like just what I need.
Jon
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
> Alejandro,
> Thanks for these responses.
> I like the unpivot one particuarly. I am just seeing if I can get it to
> work now !!
> I will let you know.
> Thanks again,
> Jon
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>|||Go It !
The two remaining columns have different data type to the other 4, Sorted.
This works really well. Thanks again.
Jon.
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:%23vWauL0iHHA.1388@.TK2MSFTNGP05.phx.gbl...
> This is working well except I am trying to go past 4 columns ( I need 6)
> I get this error when I go to 5 columns....
> The type of column "cole" conflicts with the type of other columns
> specified in the UNPIVOT list.
> with this SQL
> USE CastleProspect_Dev
> select
> ExtractNumber,rowID, RenewalDate
> from
> (select ExtractNumber,rowID,HomeRenewalMonthDay as
> a,ContentsRenewalMonthDay as b,MotorRenewalMonthDay as c,
> BreakdownRenewalMonthDay as d , PetRenewalMonthDay as e from
> dbo.ExtractDetail) as pvt
> unpivot
> (RenewalDate for aa in (a, b, c, d, e)) as unpvt
> where extractnumber = 99
> go
>
> Any Ideas '
> Thanks again. This looks like just what I need.
> Jon
> "Bishman" <jonathan.bishop@.btinternet.com> wrote in message
> news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
>> Alejandro,
>> Thanks for these responses.
>> I like the unpivot one particuarly. I am just seeing if I can get it to
>> work now !!
>> I will let you know.
>> Thanks again,
>> Jon
>>
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>>
>