SQLXML 4.0 : XPath: unable to find /Customer/Order/OrderDetail in the schema
-
Hello,
I try to run microsoft example about SQLXML 4.0 but i got this error :
XPath: unable to find /Customer/Order/OrderDetail in the schemaI use xsd describe here : Schema.xml
static string ConnString = "Provider=SQLOLEDB;Server=SQLSRVDEV;database=AdventureWorks;Integrated Security=SSPI;";
public static int testParams()
{
SqlXmlAdapter ad;
//need a memory stream to hold diff gram temporarily
MemoryStream ms = new MemoryStream();
//SqlXmlCommand cmd = new SqlXmlCommand(Connection.ToString());
SqlXmlCommand cmd = new SqlXmlCommand(ConnString);
cmd.RootTag = "ROOT";
//cmd.ClientSideXml = true;
cmd.CommandText = @"/Customer[@CustomerID=""1""]/Order/OrderDetail[@OrderQty > 3] ";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "Schema.xml";
//load data set
DataSet ds = new DataSet();
ad = new SqlXmlAdapter(cmd);
ad.Fill(ds);
return 0;
} -
Without the actual XML that you fed into your code, we have no way to see the same results that you got. I also don't see how the SQL Server is supposed to process the XML, as I don't see any paths to a local XML file other than the mention of Schema.xml, which in theory is just the schema. Chances are, the best you could get from that would be schema validation in the xml. You would have to somehow supply an actual set of xml to the code. To be honest, it might be easier with a T-SQL query. You'll have to provide a lot more details on what you are trying to accomplish.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, July 23, 2018 12:30 PM
Hoho i'm really interesting , is there other way to see relational Database as Xml One ?
I've try create a second file Query.xml
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:xpath-query name="toto" mapping-schema="SampleSchema1.xml">
/Customer[@CustomerID="29484"]/Order/OrderDetail[@OrderQty > 3]
</sql:xpath-query>
</ROOT>
And load it , using CommandStream property , no exceptions this time , but no results ^^
Output : <ROOT></ROOT>
SqlXmlCommand cmd = new SqlXmlCommand(ConnString);
cmd.RootTag = "ROOT";
//cmd.ClientSideXml = true;
cmd.SchemaPath = "SampleSchema1.xml";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.CommandStream = new FileStream("Query.xml", FileMode.Open, FileAccess.Read);using (Stream strm = cmd.ExecuteStream())
{
using (StreamReader sr = new StreamReader(strm))
{
Console.WriteLine(sr.ReadToEnd());
}
} -
I get the feeling there's a disconnect between what you want to do and how one does it. You can not just treat an entire database as if it's a bunch of XML, and use an XML query against that as a source. It just doesn't work that way. You have to have an existing set of XML and query against that XML for anything to work. Specifying an XQuery as XML and thinking that's sufficient to query a relational database - sorry - not gonna happen. Queries against XML can only operate on EXISTING xml data. A relational database can STORE xml-based data, but you have to query that xml column using T-SQL and it's xml methods. Does that make sense?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, July 23, 2018 1:00 PM
You make me Sad 🙁
I Had a dream : p11 of this thesis https://pdfs.semanticscholar.org/afed/c310254623b0b3e5a68c67b24ba072c6b254.pdf -
this one is quite good too : https://is.cuni.cz/webapps/zzp/download/150004821
- nicolas.vautrin - Monday, July 23, 2018 1:09 PM
The problem here is that you misinterpreted the nature of the problem to be solved. Take a look at this from your first link:
The XML Publishing portions makes the assumption that RDBMS data has been published as XML. AdventureWorks is NOT set up that way, and I'm doubtful anyone will be solving that kind of problem any time soon. It's not that you couldn't use an existing T-SQL query to gather an XML document from an existing set of tables in that database, but to then use an XML query of any kind against data that had to be assembled in the first place via a relational query, just doesn't make sense. Also, RDBMS databases tend to be designed around concepts first espoused by Codd and Date, and that gives you normalized data. From my perspective, this is the traditional chalkboard full of equations where a small empty area then has the caption "then a miracle occurs". Publishing normalized data in XML form with no loss in performance as compared to an RDBMS seems highly unlikely to me. It also seems as though everyone thinks that XML is the fastest way to do things, and then they try to apply it in an RDBMS and find out that's not realistic. Can't tell you how many absolutely HORRID database designs I've seen try to do that. Very bad idea most of the time.
I've based this on the appearance that you were effectively hoping that this paper was your ticket to publishing RDBMS data as XML, and that's not what the XML 4.0 code MS provides is for. It's for processing existing XML data, regardless of where it came from.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks,
You're right , i miss interpreted the thesis.
I'm a big fan of XPath , I Would like to create a REST API that could query database with XPath.
Many filter could target SubDatas of complexe and big entities , i don't wand load full entity just to filter them.
Any ideas ? 8) - nicolas.vautrin - Monday, July 23, 2018 4:36 PM
That's only going to be possible if the database contains XML data, and to be honest, that would be a horrible idea. Much faster to take relational data using a relational query. Just placing data into an XML structure rarely makes it possible to get high performance results. There's just too much string processing that has to take place, and paying that price has not been worthwhile. What the research paper seems to say is that you can build it to be "better", but "better" is often subjective as opposed to objective. You couldn't convince me that you can get better performance by querying XML data from an RDBMS than you could by querying the same data in normal form in the RDBMS, without posting the data and the queries and letting folks reproduce your documented better results first.,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply