|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Get data out of a table using a schema through select statementHello everyone,
I need to get the data out of multiple sql table joins in the form of xml using the schema. Is there a way to do that in the select statement. -- Shabnam Gupta Shabam Gupta wrote:
> I need to get the data out of multiple sql table joins in the form of xml Use FOR XML e.g.> using the schema. Is there a way to do that in the select statement. SELECT columnlist FROM table1 INNER JOIN table2 ON joincondition1 INNER JOIN table3 ON joincondition2 FOR XML AUTO If your XML needs to meet a schema then you might need to use FOR XML PATH or FOR XML EXPLICIT to make sure you get the structure you want. MSDN docs: <http://msdn2.microsoft.com/en-us/library/ms191268.aspx> <http://msdn2.microsoft.com/en-us/library/ms178107.aspx> My question is more like in vb .net or c# you can set the "Mapping Schema"
property to your annonated mapping schema and then exec the adodb.command object with command text to get the particular record or all records out in xml. Is there a way to apply the annonated mapping schema to the select statement or do I have to write a CLR stored procedure to do the schema mapping. The thing is we have this petroleum industry standard schema and I need to get the data out of the database and apply this schema to it. I was just trying to look for an easy way of doing it. Since we can input data in the database by verifying it against the schema I was trying to get it out using the schema also. Thanks Shabnam Gupta -- Show quoteShabnam Gupta "Martin Honnen" wrote: > Shabam Gupta wrote: > > > I need to get the data out of multiple sql table joins in the form of xml > > using the schema. Is there a way to do that in the select statement. > > Use FOR XML e.g. > > SELECT columnlist > FROM table1 > INNER JOIN table2 > ON joincondition1 > INNER JOIN table3 > ON joincondition2 > FOR XML AUTO > > If your XML needs to meet a schema then you might need to use FOR XML > PATH or FOR XML EXPLICIT to make sure you get the structure you want. > > MSDN docs: <http://msdn2.microsoft.com/en-us/library/ms191268.aspx> > <http://msdn2.microsoft.com/en-us/library/ms178107.aspx> > -- > > Martin Honnen --- MVP XML > http://JavaScript.FAQTs.com/ > Hello,
You can use SQLXML managed classes, take a look at http://msdn2.microsoft.com/en-us/library/aa257384(SQL.80).aspx Here is a sample code: using System; using Microsoft.Data.SqlXml; using System.IO; class Test { static string NorthwindConnString = "Provider=SQLOLEDB;Server=(local);database=Northwind;Integrated Security=SSPI"; public static int testXPath() { Stream strm; SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString); cmd.CommandText = "Emp"; cmd.CommandType = SqlXmlCommandType.XPath; cmd.RootTag = "ROOT"; cmd.SchemaPath = "MySchema.xml"; strm = cmd.ExecuteStream(); using (StreamReader sr = new StreamReader(strm)){ Console.WriteLine(sr.ReadToEnd()); } return 0; } public static int Main(String[] args) { testXPath(); return 0; } } I hope this helps. Regards, Monica Frintu Show quote "Shabam Gupta" wrote: > My question is more like in vb .net or c# you can set the "Mapping Schema" > property to your annonated mapping schema and then exec the adodb.command > object with command text to get the particular record or all records out in > xml. > Is there a way to apply the annonated mapping schema to the select statement > or do I have to write a CLR stored procedure to do the schema mapping. > > The thing is we have this petroleum industry standard schema and I need to > get the data out of the database and apply this schema to it. I was just > trying to look for an easy way of doing it. Since we can input data in the > database by verifying it against the schema I was trying to get it out using > the schema also. > > Thanks > > Shabnam Gupta > -- > Shabnam Gupta > > > "Martin Honnen" wrote: > > > Shabam Gupta wrote: > > > > > I need to get the data out of multiple sql table joins in the form of xml > > > using the schema. Is there a way to do that in the select statement. > > > > Use FOR XML e.g. > > > > SELECT columnlist > > FROM table1 > > INNER JOIN table2 > > ON joincondition1 > > INNER JOIN table3 > > ON joincondition2 > > FOR XML AUTO > > > > If your XML needs to meet a schema then you might need to use FOR XML > > PATH or FOR XML EXPLICIT to make sure you get the structure you want. > > > > MSDN docs: <http://msdn2.microsoft.com/en-us/library/ms191268.aspx> > > <http://msdn2.microsoft.com/en-us/library/ms178107.aspx> > > -- > > > > Martin Honnen --- MVP XML > > http://JavaScript.FAQTs.com/ > > Could you please cut paste the code in MySchema.xl file also.
Thanks -- Show quoteShabnam Gupta "Shabam Gupta" wrote: > Hello everyone, > > I need to get the data out of multiple sql table joins in the form of xml > using the schema. Is there a way to do that in the select statement. > -- > Shabnam Gupta Shabam Gupta wrote:
> Could you please cut paste the code in MySchema.xl file also. I think the schema for that example looks like this:<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Emp" sql:relation="Employees" > <xsd:complexType> <xsd:sequence> <xsd:element name="FName" sql:field="FirstName" type="xsd:string" /> <xsd:element name="LName" sql:field="LastName" type="xsd:string" /> </xsd:sequence> <xsd:attribute name="EmployeeID" type="xsd:integer" /> </xsd:complexType> </xsd:element> </xsd:schema> Online at <http://msdn2.microsoft.com/en-us/library/aa257507(SQL.80).aspx>. So you will need to map schema elements or attributes to SQL tables and columns using sql:relation and sql:field attributes.  |
|||||||||||||||||||||||
Other interesting topics