Home All Groups Group Topic Archive Search About

xsd annotations for xpath query

microsoft.public.sqlserver.xml
Author
6 Dec 2004 5:30 PM
Martin
Hi,

I want to query my database to get an xml doc similar to this:
<node NodeId="1" xmlns="http://tempuri.org/TreeNode2.xsd">
    <node NodeId="2">
        <node NodeId="3"></node>
    </node>
    <node NodeId="4"></node>
</node>


My database schema is as follows
CREATE TABLE [dbo].[nodes] (
[NodeID] [int] IDENTITY (1, 1) NOT NULL ,
[parentNodeId] [int] NULL ,
[text] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[nodes] ADD
CONSTRAINT [IX_nodes] UNIQUE  NONCLUSTERED
(
  [NodeID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[nodes] ADD
CONSTRAINT [FK_nodes_nodes] FOREIGN KEY
(
  [parentNodeId]
) REFERENCES [dbo].[nodes] (
  [NodeID]
)

My annotated xml schema looks like this:
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="TreeNode2" targetNamespace="http://tempuri.org/TreeNode2.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/TreeNode2.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xs:annotation>
<xs:appinfo>
<sql:relationship name="FK_nodes_nodes"
          parent="nodes"
          parent-key="Nodeid"
          child="nodes"
          child-key="parentNodeId" />
</xs:appinfo>
</xs:annotation>


<xs:complexType name="Node">
   <xs:sequence minOccurs="0" maxOccurs="unbounded">
    <xs:element name="nodee" type="Node"
        sql:relation="nodes"
        sql:relationship="FK_nodes_nodes"/>
   </xs:sequence>
  <xs:attribute name="NodeId" type="xs:string" sql:field="NodeId"/>
  <xs:attribute name="parent" type="xs:string" sql:field="parentNodeId"/>
  <xs:attribute name="text" type="xs:string" sql:field="text"/>
</xs:complexType>


<xs:element name="nodee" type="Node" sql:relation="nodes"></xs:element>
</xs:schema>

and the template looks like this:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="treeNodes2.xml">
        /nodes
    </sql:xpath-query>
</ROOT>

For the url http://localhost/Menu/template/treeNodes2T.xml I'm getting the
following error
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="XPath: unable to find /nodes in the schema"?>
  </ROOT>

Can anyone point out my error?
Thanks
Martin

Author
6 Dec 2004 11:18 PM
Michael Rys [MSFT]
Your element in the schema is called nodee instead of nodes.

Could that be the reason?

HTH
Michael

Show quote
"Martin" <x@y.z> wrote in message
news:uwCBwj72EHA.824@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I want to query my database to get an xml doc similar to this:
> <node NodeId="1" xmlns="http://tempuri.org/TreeNode2.xsd">
>    <node NodeId="2">
>        <node NodeId="3"></node>
>    </node>
>    <node NodeId="4"></node>
> </node>
>
>
> My database schema is as follows
> CREATE TABLE [dbo].[nodes] (
> [NodeID] [int] IDENTITY (1, 1) NOT NULL ,
> [parentNodeId] [int] NULL ,
> [text] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[nodes] ADD
> CONSTRAINT [IX_nodes] UNIQUE  NONCLUSTERED
> (
>  [NodeID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[nodes] ADD
> CONSTRAINT [FK_nodes_nodes] FOREIGN KEY
> (
>  [parentNodeId]
> ) REFERENCES [dbo].[nodes] (
>  [NodeID]
> )
>
> My annotated xml schema looks like this:
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="TreeNode2"
> targetNamespace="http://tempuri.org/TreeNode2.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/TreeNode2.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="FK_nodes_nodes"
>          parent="nodes"
>          parent-key="Nodeid"
>          child="nodes"
>          child-key="parentNodeId" />
> </xs:appinfo>
> </xs:annotation>
>
>
> <xs:complexType name="Node">
>   <xs:sequence minOccurs="0" maxOccurs="unbounded">
>    <xs:element name="nodee" type="Node"
>        sql:relation="nodes"
>        sql:relationship="FK_nodes_nodes"/>
>   </xs:sequence>
>  <xs:attribute name="NodeId" type="xs:string" sql:field="NodeId"/>
>  <xs:attribute name="parent" type="xs:string" sql:field="parentNodeId"/>
>  <xs:attribute name="text" type="xs:string" sql:field="text"/>
> </xs:complexType>
>
>
> <xs:element name="nodee" type="Node" sql:relation="nodes"></xs:element>
> </xs:schema>
>
> and the template looks like this:
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
>    <sql:xpath-query mapping-schema="treeNodes2.xml">
>        /nodes
>    </sql:xpath-query>
> </ROOT>
>
> For the url http://localhost/Menu/template/treeNodes2T.xml I'm getting the
> following error
> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
>  <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
> Server" Description="XPath: unable to find /nodes in the schema"?>
>  </ROOT>
>
> Can anyone point out my error?
> Thanks
> Martin
>
>
>
Author
7 Dec 2004 9:01 AM
Martin
Oops I've tried different combinations.

with
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="treeNodes2.xml">
        /nodee
    </sql:xpath-query>
</ROOT>

It fails like this:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL
Server" Description="XPath: unable to find /nodee in the schema"?>
  </ROOT>

Still stuck
Martin
Show quote
"Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message
news:%23kDMun%232EHA.1264@TK2MSFTNGP12.phx.gbl...
> Your element in the schema is called nodee instead of nodes.
>
> Could that be the reason?
>
> HTH
> Michael
>
> "Martin" <x@y.z> wrote in message
> news:uwCBwj72EHA.824@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I want to query my database to get an xml doc similar to this:
> > <node NodeId="1" xmlns="http://tempuri.org/TreeNode2.xsd">
> >    <node NodeId="2">
> >        <node NodeId="3"></node>
> >    </node>
> >    <node NodeId="4"></node>
> > </node>
> >
> >
> > My database schema is as follows
> > CREATE TABLE [dbo].[nodes] (
> > [NodeID] [int] IDENTITY (1, 1) NOT NULL ,
> > [parentNodeId] [int] NULL ,
> > [text] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[nodes] ADD
> > CONSTRAINT [IX_nodes] UNIQUE  NONCLUSTERED
> > (
> >  [NodeID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[nodes] ADD
> > CONSTRAINT [FK_nodes_nodes] FOREIGN KEY
> > (
> >  [parentNodeId]
> > ) REFERENCES [dbo].[nodes] (
> >  [NodeID]
> > )
> >
> > My annotated xml schema looks like this:
> > <?xml version="1.0" encoding="utf-8" ?>
> > <xs:schema id="TreeNode2"
> > targetNamespace="http://tempuri.org/TreeNode2.xsd"
> > elementFormDefault="qualified"
> > xmlns="http://tempuri.org/TreeNode2.xsd"
> > xmlns:xs="http://www.w3.org/2001/XMLSchema"
> > xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> >
> > <xs:annotation>
> > <xs:appinfo>
> > <sql:relationship name="FK_nodes_nodes"
> >          parent="nodes"
> >          parent-key="Nodeid"
> >          child="nodes"
> >          child-key="parentNodeId" />
> > </xs:appinfo>
> > </xs:annotation>
> >
> >
> > <xs:complexType name="Node">
> >   <xs:sequence minOccurs="0" maxOccurs="unbounded">
> >    <xs:element name="nodee" type="Node"
> >        sql:relation="nodes"
> >        sql:relationship="FK_nodes_nodes"/>
> >   </xs:sequence>
> >  <xs:attribute name="NodeId" type="xs:string" sql:field="NodeId"/>
> >  <xs:attribute name="parent" type="xs:string" sql:field="parentNodeId"/>
> >  <xs:attribute name="text" type="xs:string" sql:field="text"/>
> > </xs:complexType>
> >
> >
> > <xs:element name="nodee" type="Node" sql:relation="nodes"></xs:element>
> > </xs:schema>
> >
> > and the template looks like this:
> > <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> >    <sql:xpath-query mapping-schema="treeNodes2.xml">
> >        /nodes
> >    </sql:xpath-query>
> > </ROOT>
> >
> > For the url http://localhost/Menu/template/treeNodes2T.xml I'm getting
the
> > following error
> > <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> >  <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to
SQL
> > Server" Description="XPath: unable to find /nodes in the schema"?>
> >  </ROOT>
> >
> > Can anyone point out my error?
> > Thanks
> > Martin
> >
> >
> >
>
>
Author
7 Dec 2004 11:12 AM
Martin
This annotated schema works:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="MyRel"
          parent="nodes"
          parent-key="NodeID"
          child="nodes"
          child-key="parentNodeId" />
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="node" sql:relation="nodes" type="nodeType"
sql:key-fields="NodeID" sql:limit-field="parentNodeId"  />

   <xsd:complexType name="nodeType" >
     <xsd:sequence minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="node" type="nodeType"
   sql:key-fields="NodeID"
                     sql:relation="nodes"
                    sql:relationship="MyRel"
     sql:max-depth="6"  >
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="NodeID"   type="xsd:ID" />

<xsd:attribute name="text" type="xsd:string" />
    </xsd:complexType>

</xsd:schema>

Thanks
Martin

Show quote
"Michael Rys [MSFT]" <m***@online.microsoft.com> wrote in message
news:%23kDMun%232EHA.1264@TK2MSFTNGP12.phx.gbl...
> Your element in the schema is called nodee instead of nodes.
>
> Could that be the reason?
>
> HTH
> Michael
>
> "Martin" <x@y.z> wrote in message
> news:uwCBwj72EHA.824@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I want to query my database to get an xml doc similar to this:
> > <node NodeId="1" xmlns="http://tempuri.org/TreeNode2.xsd">
> >    <node NodeId="2">
> >        <node NodeId="3"></node>
> >    </node>
> >    <node NodeId="4"></node>
> > </node>
> >
> >
> > My database schema is as follows
> > CREATE TABLE [dbo].[nodes] (
> > [NodeID] [int] IDENTITY (1, 1) NOT NULL ,
> > [parentNodeId] [int] NULL ,
> > [text] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[nodes] ADD
> > CONSTRAINT [IX_nodes] UNIQUE  NONCLUSTERED
> > (
> >  [NodeID]
> > )  ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[nodes] ADD
> > CONSTRAINT [FK_nodes_nodes] FOREIGN KEY
> > (
> >  [parentNodeId]
> > ) REFERENCES [dbo].[nodes] (
> >  [NodeID]
> > )
> >
> > My annotated xml schema looks like this:
> > <?xml version="1.0" encoding="utf-8" ?>
> > <xs:schema id="TreeNode2"
> > targetNamespace="http://tempuri.org/TreeNode2.xsd"
> > elementFormDefault="qualified"
> > xmlns="http://tempuri.org/TreeNode2.xsd"
> > xmlns:xs="http://www.w3.org/2001/XMLSchema"
> > xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> >
> > <xs:annotation>
> > <xs:appinfo>
> > <sql:relationship name="FK_nodes_nodes"
> >          parent="nodes"
> >          parent-key="Nodeid"
> >          child="nodes"
> >          child-key="parentNodeId" />
> > </xs:appinfo>
> > </xs:annotation>
> >
> >
> > <xs:complexType name="Node">
> >   <xs:sequence minOccurs="0" maxOccurs="unbounded">
> >    <xs:element name="nodee" type="Node"
> >        sql:relation="nodes"
> >        sql:relationship="FK_nodes_nodes"/>
> >   </xs:sequence>
> >  <xs:attribute name="NodeId" type="xs:string" sql:field="NodeId"/>
> >  <xs:attribute name="parent" type="xs:string" sql:field="parentNodeId"/>
> >  <xs:attribute name="text" type="xs:string" sql:field="text"/>
> > </xs:complexType>
> >
> >
> > <xs:element name="nodee" type="Node" sql:relation="nodes"></xs:element>
> > </xs:schema>
> >
> > and the template looks like this:
> > <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> >    <sql:xpath-query mapping-schema="treeNodes2.xml">
> >        /nodes
> >    </sql:xpath-query>
> > </ROOT>
> >
> > For the url http://localhost/Menu/template/treeNodes2T.xml I'm getting
the
> > following error
> > <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
> >  <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to
SQL
> > Server" Description="XPath: unable to find /nodes in the schema"?>
> >  </ROOT>
> >
> > Can anyone point out my error?
> > Thanks
> > Martin
> >
> >
> >
>
>

AddThis Social Bookmark Button

Post Other interesting topics
Capture FOR XML output in a local string variable?
SQLXMLBulkLoad with VB .Net - No errors but data is not loading
generating XML to validated against the schema
XML files with bad characters
sp_OAMethod and long text return from COM Dll method