Can not get notification after modifying ADF.xml?

I reregister and restart my instances after modifying ADF.xml,but still have no any of notifications

any idea about that? Thanks

what should i do after tuning ADF.xml or ICF.xml?

Thanks in advance

|||

After modifying your source xml documents, the ICF and ADF, you should update the instance (right click, Tasks, update in SSMS or NSControl update from the command line) to modify the instance. Check the output in either case to make sure the changes were successfully applied.

If you've modified the SubscriptionClass, rather than altering the underlying subscription table, the update process renames the existing table and creates a new one. The new one is empty, without rows. You'll need to manually migrate the subscription data to the new table, taking into account the altered structure. The old subscription table will have an appended "Old" to its name.

HTH...

Joe

Can not find Microsoft.SqlServer.InstApi when trying to run a SMO trace

I install the follwing components

1.Microsoft SQL Server 2005 Management Objects Collection

2.Microsoft Core XML Services (MSXML) 6.0

3.Microsoft SQL Server Native Client

(available from http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en)

on a clean machine with no SQL Server 2005 client tools.

I get the following error.

Failed to initialize object as reader.
Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

A gacutil tells me that this assembly is not installed.

Is there are way to run SMO trace without having to install the 2005 client tools and only the Management Objects Collection and necessary components?

Sample code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Trace;
using Microsoft.SqlServer.Management.Common;

namespace SMOTraceTestProgram
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void bStart_Click(object sender, EventArgs e)
{

TraceServer smoTraceServerReader;

ConnectionInfoBase cInfoBase = new SqlConnectionInfo();
SetupConnectionInfoBase((cInfoBase as SqlConnectionInfo));

smoTraceServerReader = new TraceServer();

try
{
smoTraceServerReader.InitializeAsReader(cInfoBase, "2005NOSPIDFilter.tdf");
smoTraceServerReader.Stop();
MessageBox.Show("Trace Started and stopped successfully");
}
catch (Exception exception)
{
StringBuilder sb = new StringBuilder();
sb.Append(exception.Message);

Exception ex = exception.InnerException;
while(ex != null)
{
sb.Append(ex.Message);
ex = ex.InnerException;
}
MessageBox.Show(sb.ToString());
}

}

private void SetupConnectionInfoBase(SqlConnectionInfo sqlConnectionInfo)
{
sqlConnectionInfo.ServerName = eSeverName.Text;

if (eUserName.Text.Length > 0)
{
sqlConnectionInfo.UserName = eUserName.Text;
sqlConnectionInfo.Password = ePassword.Text;
sqlConnectionInfo.UseIntegratedSecurity = false;
}
else
{
sqlConnectionInfo.UseIntegratedSecurity = true;
}

}

}
}

I have reproduced this issue in house and can confirm that this is a problem with the Microsoft SQL Server 2005 Management Objects Collection. We will consider this issue for inclusion in Service Pack 2.

As for workarounds, I don't have one. I tried Gac'cing the missing DLL, but there is apparently more registration required, as evidenced by the new error message I got. ("Failed to initialize object as reader.Failed to get SQL Tools directory path from InstAPI.")

|||

Thanks James,

Appreciate the feedback. For the time being I will get the users of my profiling tool to install management tools from the SQL Server 2005 Install. This seems to install the necessary components for SMO Trace to work.

Look forward to a fix though, as this installs a lot more than is required.

sql

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 trust Microsoft for FOR XML AUTO or FOR XML RAW ?

Hi,
I have been there a situation of an apprehension that Microsoft may issue
some patch or hotfix in future for SQL SERVER, that will change the shape of
XML results yielded by FOR XML AUTO or FOR XML RAW query.
Our query is going to be rigid in the application and the data would then be
passed through sensitive application that may crash if xml is not valid, we
have many types of xmls so we cannot create schema for each and every guy an
d
same with EXPLICIT.
Is this superstition valid that I shouldnt trust Microsoft here ?
Any input in this will sincerely be appreciated.
Fahad"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
FOR XML AUTO and FOR XML RAW results are pretty well defined, since SQL
2000. It's doubtful these would change in the current version of SQL Server
(2005) or espcially in 2000. A lot of people have already written
applications that use this functionality which would surely break if they
changed it. However the results are subject to change if your schema
changes which is probably a much more likely situation.

> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
I would recommend using FOR XML PATH if you have SQL 2005. It's easy to
use, uses XPath notation to define your XML, and you can easily update or
modify it if your schema changes.

> Is this superstition valid that I shouldnt trust Microsoft here ?
Like I said, if they change this in a Service Pack (and it's been around
since SQL 2000), they are going to have a lot of unhappy customers who have
invested a lot of time and money in building applications to use it. OTOH,
one could say the same thing about COM and VB 6 :) At any rate I wouldn't
worry too much about it until at least the next major version release.|||"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
I know Microsoft occasionally make breaking changes but normally they go the
other way, everything has to be backwards compatible even to the detriment
of the latest version.
Have you any specific reason for worrying that the FOR XML statements are
going to be modified?
Joe Fawcett (MVP - XML)
http://joe.fawcett.name|||This seems like a strange thing to worry about. Aside from the basic issue
of how else would you represent a simple result set as XML, what motivation
would there be to ever changing this? What would happen if Microsoft did
this? There would be thousands of blog entries, newsgroup postings, and
news articles vilifying Microsoft for sticking it to the little guy, the
stock would drop, hundreds of employees would have to change their
retirement plans, and the SQL team would be hated by the rest of Microsoft.
Contrast this with the alternative of not changing code that works well
already and it makes changing the format a poor alternative.
Reading between the lines of your post makes me worry that you plan to do
some kind of roll your own text parsing instead of using a real XML parser.
If this is true then there could be an issue. While the results will be
semantically the same, it's hard to guarantee character for character
compatibility forever. If you want to do your own parsing you are better
off using something like comma separated values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Reading between the lines of your post makes me worry that you plan to do
> some kind of roll your own text parsing instead of using a real XML
> parser. If this is true then there could be an issue. While the results
> will be semantically the same, it's hard to guarantee character for
> character compatibility forever. If you want to do your own parsing you
> are better off using something like comma separated values.
Do you see this being a problem if the OP is using Unicode?|||Could you expand a bit on what you mean? I'm not sure how Unicode fits into
the question.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:%238jjscTSHHA.5060@.TK2MSFTNGP06.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Do you see this being a problem if the OP is using Unicode?
>|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Could you expand a bit on what you mean? I'm not sure how Unicode fits
> into the question.
"While the results will be semantically the same, it's hard to guarantee
character for character compatibility forever."
I assumed you were talking about character sets, code pages, etc., in your
"character for character compatibility" statement. Did I misunderstand?|||No, I was talking about white space and namespace prefixes, etc. These are
the things that break when people write their own parsers instead of using a
full XML parser.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:uvLHAwWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> "While the results will be semantically the same, it's hard to guarantee
> character for character compatibility forever."
> I assumed you were talking about character sets, code pages, etc., in your
> "character for character compatibility" statement. Did I misunderstand?
>|||As the program manager still responsible for FOR XML, we are not planning on
changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
we guard it by a backwards-compatibility flag and will document the changed
behaviour ahead of time).
Between SQL Server 2000 and 2005 we did a few changes, mostly along the
lines of entitization changes. We had one breaking change in how FOR XML
AUTO dealt with subqueries and views that caused some pain, but was actually
a bug fix in the design of FOR XML AUTO's heuristics.
So if you can be more specific of what changes you fear, I can give you a
more definitive answer.
Changes that may happen in the future include addition or removal or certain
namespace declarations, changing order of attributes. But we are not
planning on changing the resultshapes of the different modes.
Best regards
Michael
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>|||Thankyou very much for your interest in helping me Michael and other guys to
o.
Well I am getting simple shaped XML from a single table which we then
transform using XSL. The changes which I am anticipating include
Adding any namespace to data element that will make our XPath expressions
useless. We will NEVER be able to update XPaths and issue new release of our
software once it is burnt into the hardware.
Changes in the source tree, again it will require us to update XSL.
"Michael Rys [MSFT]" wrote:

> As the program manager still responsible for FOR XML, we are not planning
on
> changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unles
s
> we guard it by a backwards-compatibility flag and will document the change
d
> behaviour ahead of time).
> Between SQL Server 2000 and 2005 we did a few changes, mostly along the
> lines of entitization changes. We had one breaking change in how FOR XML
> AUTO dealt with subqueries and views that caused some pain, but was actual
ly
> a bug fix in the design of FOR XML AUTO's heuristics.
> So if you can be more specific of what changes you fear, I can give you a
> more definitive answer.
> Changes that may happen in the future include addition or removal or certa
in
> namespace declarations, changing order of attributes. But we are not
> planning on changing the resultshapes of the different modes.
> Best regards
> Michael
> "Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in messag
e
> news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
>
>

Can I trust Microsoft for FOR XML AUTO or FOR XML RAW ?

Hi,
I have been there a situation of an apprehension that Microsoft may issue
some patch or hotfix in future for SQL SERVER, that will change the shape of
XML results yielded by FOR XML AUTO or FOR XML RAW query.
Our query is going to be rigid in the application and the data would then be
passed through sensitive application that may crash if xml is not valid, we
have many types of xmls so we cannot create schema for each and every guy and
same with EXPLICIT.
Is this superstition valid that I shouldnt trust Microsoft here ?
Any input in this will sincerely be appreciated.
Fahad
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
FOR XML AUTO and FOR XML RAW results are pretty well defined, since SQL
2000. It's doubtful these would change in the current version of SQL Server
(2005) or espcially in 2000. A lot of people have already written
applications that use this functionality which would surely break if they
changed it. However the results are subject to change if your schema
changes which is probably a much more likely situation.

> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
I would recommend using FOR XML PATH if you have SQL 2005. It's easy to
use, uses XPath notation to define your XML, and you can easily update or
modify it if your schema changes.

> Is this superstition valid that I shouldnt trust Microsoft here ?
Like I said, if they change this in a Service Pack (and it's been around
since SQL 2000), they are going to have a lot of unhappy customers who have
invested a lot of time and money in building applications to use it. OTOH,
one could say the same thing about COM and VB 6 At any rate I wouldn't
worry too much about it until at least the next major version release.
|||"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
I know Microsoft occasionally make breaking changes but normally they go the
other way, everything has to be backwards compatible even to the detriment
of the latest version.
Have you any specific reason for worrying that the FOR XML statements are
going to be modified?

Joe Fawcett (MVP - XML)
http://joe.fawcett.name
|||This seems like a strange thing to worry about. Aside from the basic issue
of how else would you represent a simple result set as XML, what motivation
would there be to ever changing this? What would happen if Microsoft did
this? There would be thousands of blog entries, newsgroup postings, and
news articles vilifying Microsoft for sticking it to the little guy, the
stock would drop, hundreds of employees would have to change their
retirement plans, and the SQL team would be hated by the rest of Microsoft.
Contrast this with the alternative of not changing code that works well
already and it makes changing the format a poor alternative.
Reading between the lines of your post makes me worry that you plan to do
some kind of roll your own text parsing instead of using a real XML parser.
If this is true then there could be an issue. While the results will be
semantically the same, it's hard to guarantee character for character
compatibility forever. If you want to do your own parsing you are better
off using something like comma separated values.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Reading between the lines of your post makes me worry that you plan to do
> some kind of roll your own text parsing instead of using a real XML
> parser. If this is true then there could be an issue. While the results
> will be semantically the same, it's hard to guarantee character for
> character compatibility forever. If you want to do your own parsing you
> are better off using something like comma separated values.
Do you see this being a problem if the OP is using Unicode?
|||Could you expand a bit on what you mean? I'm not sure how Unicode fits into
the question.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:%238jjscTSHHA.5060@.TK2MSFTNGP06.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:OpxtSS7RHHA.4832@.TK2MSFTNGP03.phx.gbl...
> Do you see this being a problem if the OP is using Unicode?
>
|||"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Could you expand a bit on what you mean? I'm not sure how Unicode fits
> into the question.
"While the results will be semantically the same, it's hard to guarantee
character for character compatibility forever."
I assumed you were talking about character sets, code pages, etc., in your
"character for character compatibility" statement. Did I misunderstand?
|||No, I was talking about white space and namespace prefixes, etc. These are
the things that break when people write their own parsers instead of using a
full XML parser.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:uvLHAwWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:O8Ac8EUSHHA.3592@.TK2MSFTNGP06.phx.gbl...
> "While the results will be semantically the same, it's hard to guarantee
> character for character compatibility forever."
> I assumed you were talking about character sets, code pages, etc., in your
> "character for character compatibility" statement. Did I misunderstand?
>
|||As the program manager still responsible for FOR XML, we are not planning on
changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
we guard it by a backwards-compatibility flag and will document the changed
behaviour ahead of time).
Between SQL Server 2000 and 2005 we did a few changes, mostly along the
lines of entitization changes. We had one breaking change in how FOR XML
AUTO dealt with subqueries and views that caused some pain, but was actually
a bug fix in the design of FOR XML AUTO's heuristics.
So if you can be more specific of what changes you fear, I can give you a
more definitive answer.
Changes that may happen in the future include addition or removal or certain
namespace declarations, changing order of attributes. But we are not
planning on changing the resultshapes of the different modes.
Best regards
Michael
"Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
> Hi,
> I have been there a situation of an apprehension that Microsoft may issue
> some patch or hotfix in future for SQL SERVER, that will change the shape
> of
> XML results yielded by FOR XML AUTO or FOR XML RAW query.
>
> Our query is going to be rigid in the application and the data would then
> be
> passed through sensitive application that may crash if xml is not valid,
> we
> have many types of xmls so we cannot create schema for each and every guy
> and
> same with EXPLICIT.
> Is this superstition valid that I shouldnt trust Microsoft here ?
>
> Any input in this will sincerely be appreciated.
>
> Fahad
>
|||Thankyou very much for your interest in helping me Michael and other guys too.
Well I am getting simple shaped XML from a single table which we then
transform using XSL. The changes which I am anticipating include
Adding any namespace to data element that will make our XPath expressions
useless. We will NEVER be able to update XPaths and issue new release of our
software once it is burnt into the hardware.
Changes in the source tree, again it will require us to update XSL.
"Michael Rys [MSFT]" wrote:

> As the program manager still responsible for FOR XML, we are not planning on
> changing the XML infoset generated by FOR XML RAW, EXPLICIT or PATH (unless
> we guard it by a backwards-compatibility flag and will document the changed
> behaviour ahead of time).
> Between SQL Server 2000 and 2005 we did a few changes, mostly along the
> lines of entitization changes. We had one breaking change in how FOR XML
> AUTO dealt with subqueries and views that caused some pain, but was actually
> a bug fix in the design of FOR XML AUTO's heuristics.
> So if you can be more specific of what changes you fear, I can give you a
> more definitive answer.
> Changes that may happen in the future include addition or removal or certain
> namespace declarations, changing order of attributes. But we are not
> planning on changing the resultshapes of the different modes.
> Best regards
> Michael
> "Fahad Ashfaque" <FahadAshfaque@.discussions.microsoft.com> wrote in message
> news:86415A5A-428D-48DF-9175-114E9E0030F7@.microsoft.com...
>
>

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.
You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn/...l/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>
|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.
|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegr oups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>
|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.

Can I query XML data in SQL Server 2005 and get aggregate information? If so, how?

Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn...ml/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegroups.com:

> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.

>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.

>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegroups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.

Can I just get a string back from SQL Server with "FOR XML AUTO, ELEMENTS"?

I keep getting back some hexadecimal garbage data rather than the nice XML I
see in the Query Analyzer.
What gives?
WTH
Are you using OLEDB or ADO?
You should use the command STREAM object instead of the recordset. See the
documentation for some example code.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
>I keep getting back some hexadecimal garbage data rather than the nice XML
>I
> see in the Query Analyzer.
> What gives?
> WTH
>
|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Are you using OLEDB or ADO?
> You should use the command STREAM object instead of the recordset. See the
> documentation for some example code.
> Best regards
> Michael
> "WTH" <nospam@.spamtheman.com> wrote in message
> news:eUPcJDRwEHA.356@.TK2MSFTNGP10.phx.gbl...
XML[vbcol=seagreen]
I read that using that method results in differing results depending upon
whether a remote server returns the results or a local server does, no
thanks.
I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
WTH
|||> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
There is no problem that I am aware off regarding connecting to a local or
remote server using the command stream object and FOR XML. Do you have a
reference to this?
However, if your other solution works, that's fine.
Best regards
Michael
"WTH" <nospam@.spamtheman.com> wrote in message
news:emhf28RwEHA.3808@.TK2MSFTNGP15.phx.gbl...
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OFN0lnRwEHA.3668@.tk2msftngp13.phx.gbl...
> XML
> I read that using that method results in differing results depending upon
> whether a remote server returns the results or a local server does, no
> thanks.
> I just decided to pull off the "FOR XML AUTO, ELEMENTS" and simply use a
> CXMLAccessor OleDB Accessor class and call ::GetXMLRowData instead.
> WTH
>