Having problems with Typed Xml

Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
1- I've created a "Business" Database
USE [Business]
GO
2- I've defined an Xml Schema Collection
create xml schema collection dbo.CustomerFile as
'<xs:schema xmlns="http://XmlSpaces.Tests"
targetNamespace="http://XmlSpaces.Tests"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="EntityType">
<xs:sequence />
<xs:attribute name="key" type="xs:string" />
<xs:attribute name="reference" type="xs:string" />
<xs:attribute name="creationDate" type="xs:dateTime" />
</xs:complexType>
<xs:simpleType name="CivilityCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Mr" />
<xs:enumeration value="Mm" />
<xs:enumeration value="Ml" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="CivilityType">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="code" type="CivilityCodification" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="NameType">
<xs:sequence>
<xs:element name="Civility" type="CivilityType" />
<xs:element name="First" type="xs:string" />
<xs:element name="Last" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="Street" type="xs:string" />
<xs:element name="Zip" type="xs:string" />
<xs:element name="Town" type="xs:string" />
</xs:sequence>
<xs:attribute name="code" type="AddressCodification" />
</xs:complexType>
<xs:simpleType name="AddressCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Main" />
<xs:enumeration value="Office" />
<xs:enumeration value="Home" />
</xs:restriction>
</xs:simpleType>
<xs:element name="Address" type="AddressType" />
<xs:complexType name="CustomerType">
<xs:complexContent mixed="false">
<xs:extension base="EntityType">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="Name"
type="NameType" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:element name="Customer" type="CustomerType" />
</xs:schema>'
3- I've build a table that use this Schema definition
CREATE TABLE [dbo].[Customers]
(
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
(
[OID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
4- From what i understood the following statement should work but it doensn't
INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
'<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
xmlns="http://XmlSpaces.Tests">
<Name>
<Civility code="Mr">Mister</Civility>
<First>Brice</First>
<Last>Prunier</Last>
</Name>
<Address code="Main">
<Street>66 street A</Street>
<Zip>11111</Zip>
<Town>Seattle</Town>
</Address>
<Address code="Office">
<Street>33 Avenue B</Street>
<Zip>22222</Zip>
<Town>Los Angeles</Town>
</Address>
</Customer>' )
Trying the here above context raised the followings error
1- invalid format for Customer/@.creationDate
After switching xs:dateTime to xs:string i get :
Expected element : Name, http://XmlSpaces.Tests:Address where element
http://XmlSpaces.Tests:Name was specified
thanks for help
First issue:
In SQL Server 2005, we require a timezone on the value. So you either need
to change the type in the schema to xs:string (as you did) or add a timezone
to the value (e.g., append a Z). Also, in the April CTP (and June CTP) we do
not yet round to the millisecond precision but raise an error (we are
looking into changing that, although at this stage any functional change
needs lots of justification). So you should reduce the microseconds to
milliseconds.
Second issue:
You need to add
elementFormDefault="qualified"
to the xs:schema element.
Best regards
Michael
"Brice Prunier" <Brice Prunier@.discussions.microsoft.com> wrote in message
news:E28FD8F9-50CD-446D-8A0A-BB2775114B64@.microsoft.com...
> Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
> 1- I've created a "Business" Database
> USE [Business]
> GO
> 2- I've defined an Xml Schema Collection
> create xml schema collection dbo.CustomerFile as
> '<xs:schema xmlns="http://XmlSpaces.Tests"
> targetNamespace="http://XmlSpaces.Tests"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="EntityType">
> <xs:sequence />
> <xs:attribute name="key" type="xs:string" />
> <xs:attribute name="reference" type="xs:string" />
> <xs:attribute name="creationDate" type="xs:dateTime" />
> </xs:complexType>
> <xs:simpleType name="CivilityCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Mr" />
> <xs:enumeration value="Mm" />
> <xs:enumeration value="Ml" />
> </xs:restriction>
> </xs:simpleType>
> <xs:complexType name="CivilityType">
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="code" type="CivilityCodification" />
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> <xs:complexType name="NameType">
> <xs:sequence>
> <xs:element name="Civility" type="CivilityType" />
> <xs:element name="First" type="xs:string" />
> <xs:element name="Last" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="AddressType">
> <xs:sequence>
> <xs:element name="Street" type="xs:string" />
> <xs:element name="Zip" type="xs:string" />
> <xs:element name="Town" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="code" type="AddressCodification" />
> </xs:complexType>
> <xs:simpleType name="AddressCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Main" />
> <xs:enumeration value="Office" />
> <xs:enumeration value="Home" />
> </xs:restriction>
> </xs:simpleType>
> <xs:element name="Address" type="AddressType" />
> <xs:complexType name="CustomerType">
> <xs:complexContent mixed="false">
> <xs:extension base="EntityType">
> <xs:sequence>
> <xs:element minOccurs="0" maxOccurs="1" name="Name"
> type="NameType" />
> <xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> <xs:element name="Customer" type="CustomerType" />
> </xs:schema>'
> 3- I've build a table that use this Schema definition
> CREATE TABLE [dbo].[Customers]
> (
> [OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
> [Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> [XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
> CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
> (
> [OID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> 4- From what i understood the following statement should work but it
> doensn't
> INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
> '<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
> xmlns="http://XmlSpaces.Tests">
> <Name>
> <Civility code="Mr">Mister</Civility>
> <First>Brice</First>
> <Last>Prunier</Last>
> </Name>
> <Address code="Main">
> <Street>66 street A</Street>
> <Zip>11111</Zip>
> <Town>Seattle</Town>
> </Address>
> <Address code="Office">
> <Street>33 Avenue B</Street>
> <Zip>22222</Zip>
> <Town>Los Angeles</Town>
> </Address>
> </Customer>' )
> Trying the here above context raised the followings error
> 1- invalid format for Customer/@.creationDate
> After switching xs:dateTime to xs:string i get :
> Expected element : Name, http://XmlSpaces.Tests:Address where element
> http://XmlSpaces.Tests:Name was specified
> thanks for help
>

Having problems with SQLXML bulk import using diffgram

I have a program that writes out xml files that I use to import into SQL
server. When I start importing, most of the files that I have successfully
import, but some of them error out with the message: "SQLOLEDB Error
Description: Empty update, no updatable rows found Transaction aborted".
I've looked at the xml diffgram and there are definitely modified and
inserted rows in it. Has anyone run across this? I'd be glad to post the
diffgram here if anyone would like to see it.
Thanks,
Rob Baber
Do you use the diffgram or updategram format?
Sending a repro would help.
Thanks
Michael
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber
|||Could you please check the generated T-SQL by the SqlXml by using the
profiler? You are most likely using a diffgram behaving different than your
expectation or hitting a bug in SqlXml.
If you check out the sql and it seems like a wrongly generated one, could
you please post your diffgram and t-sql so that we can investigate if it is
a bug.
Thanks
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber
sql

Having problems with SQLXML bulk import using diffgram

I have a program that writes out xml files that I use to import into SQL
server. When I start importing, most of the files that I have successfully
import, but some of them error out with the message: "SQLOLEDB Error
Description: Empty update, no updatable rows found Transaction aborted".
I've looked at the xml diffgram and there are definitely modified and
inserted rows in it. Has anyone run across this? I'd be glad to post the
diffgram here if anyone would like to see it.
Thanks,
Rob BaberDo you use the diffgram or updategram format?
Sending a repro would help.
Thanks
Michael
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber|||Could you please check the generated T-SQL by the SqlXml by using the
profiler? You are most likely using a diffgram behaving different than your
expectation or hitting a bug in SqlXml.
If you check out the sql and it seems like a wrongly generated one, could
you please post your diffgram and t-sql so that we can investigate if it is
a bug.
Thanks
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Baber" <Rob Baber@.discussions.microsoft.com> wrote in message
news:E10640A7-BCE5-42AE-B842-C9956E067812@.microsoft.com...
>I have a program that writes out xml files that I use to import into SQL
> server. When I start importing, most of the files that I have
> successfully
> import, but some of them error out with the message: "SQLOLEDB Error
> Description: Empty update, no updatable rows found Transaction aborted".
> I've looked at the xml diffgram and there are definitely modified and
> inserted rows in it. Has anyone run across this? I'd be glad to post the
> diffgram here if anyone would like to see it.
> Thanks,
> Rob Baber

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuing
multiple queries by breaking them up via WHERE clause filters isn’t the way I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
..NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
Scott
The XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommandforXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReadersr1 = new StreamReader(mQueryFileName);
stringquery = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.Peek() >= 0)
{
c = new char[10000];
intnumRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Handling very large XML result sets

I am writing a .NET based application to create large XML data files using
SQLXML classes and FOR XML EXPLICIT queries. What are some strategies I can
use to break up and process these large result sets? The overhead of issuin
g
multiple queries by breaking them up via WHERE clause filters isn’t the wa
y I
want to go since my queries are very large and take significant time to
process within SQL server.
I am currently experiencing out of memory exceptions on some larger result
sets (~50-60 Mbytes total XML file size). My first attempt was using
SqlXmlCommand.ExecuteXmlReader and an XmlDocument via this snippet of code:
XmlReader xr = forXMLCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
This throws a System.OutOfMemoryException on the call to ExecuteXmlReader
when the result set gets very large.
I also tried using SqlXmlCommand.ExecuteStream thinking I could read a
buffer of chars at a time to process these large result sets but this also
resulted in a System.OutOfMemoryException on the call to ExecuteStream:
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.P() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
I have tried running my application on two different systems one with 1G of
main memory and the other a Win2K3 server with 8G of main memory. Both
systems seem to run out of memory at the same 50-60 Mb limit) Are there any
.NET memory settings I can tweak to give my .NET application more memory?
Thanks for your suggestions and ideas,
ScottThe XmlReader is a streaming interface which should not run out of memory
via the SqlXmlCommand.ExecuteStream method.
Loading into an XmlDocument however will cache the entire document into
memory.
Can you remove the following two lines from your repro and see if you are
still having the problem:
XmlDocument xd = new XmlDocument();
xd.Load(xr);
Thanks -
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||Andrew,
That was exactly my thought as well, but ExecuteStream is throwing an
OutOfMemoryException. I am NOTcalling XmlDocument .Load in the code that
uses ExecuteStream.
Here is my full method I am using:
private void ExecuteSQLXMLCommandExecuteStream()
{
try
{
SqlXmlCommand forXMLCommand = new SqlXmlCommand("Provider=SQLOLEDB;DATA
SOURCE=Gibraltar;Initial Catalog=RDCModel;User ID=sa;Password=XXXX");
forXMLCommand.CommandType = SqlXmlCommandType.Sql;
StreamReader sr1 = new StreamReader(mQueryFileName);
string query = sr1.ReadToEnd();
sr1.Close();
query = query.Replace("\r\n", " ");
query = query.Replace("\t", " ");
forXMLCommand.CommandText = query;
Stream s = forXMLCommand.ExecuteStream();
StreamWriter sw3 = new StreamWriter(mResultsFileName);
using (StreamReader sr = new StreamReader(s))
{
char[] c = null;
while (sr.P() >= 0)
{
c = new char[10000];
int numRead = sr.Read(c, 0, c.Length);
sw3.Write(c, 0, numRead);
}
}
sw3.Close();
}
catch (SqlXmlException ex)
{
ex.ErrorStream.Position = 0;
string sqlErrorString;
sqlErrorString = new StreamReader(ex.ErrorStream).ReadToEnd();
Console.WriteLine(sqlErrorString);
RDCUtilities.WriteToLog(sqlErrorString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
RDCUtilities.WriteToLog(ex.Message);
}
""Andrew Conrad"" wrote:

> The XmlReader is a streaming interface which should not run out of memory
> via the SqlXmlCommand.ExecuteStream method.
> Loading into an XmlDocument however will cache the entire document into
> memory.
> Can you remove the following two lines from your repro and see if you are
> still having the problem:
> XmlDocument xd = new XmlDocument();
> xd.Load(xr);
> Thanks -
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||Try using SqlXmlCommand.ExecuteToStream() instead of ExecuteStream.
Because of some technical limitations with COM interop, ExecuteStream
caches results.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
Regards
Sql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards
|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.
|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.FullName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlog xml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
?
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works?
COMMIT;
++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBl ocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagemen tQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogo n_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
..net\exception
management\code\cs\exceptionmanagementquickstartsa mples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/
|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
RegardsSql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
--
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.FullName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlogxml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
'
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works'
COMMIT;

++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBlocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagementQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogon_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
.net\exception
management\code\cs\exceptionmanagementqu
ickstartsamples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling GUID/UniqueIdentifier

Hi,

I am presently working on a ETL Process of importing data from XML source to the database (SQL Server 2000/2005).

I have GUID data in the XML file and i need to import that data into the database tables.

My Clarification is, if i import the GUID from XML to the database table, in the future, if the Database Engine generates new GUID's for new data, is there a posibility that the database engine might generate SAME guid as the one i imported from XML? As the GUID's in XML were not generated by the target Database Engine, can the database engine possibly generate the same GUID similar to the one i imported from XML?

Regards,

Vikram

If the Guid column in your table has a UNIQUE constraint applied, then you will have no problem.|||GUIDs have a theoritical possibility of duplication once every 100 years somewhere in one computer in the istalled base of every computer in the world. (approximate)|||

Thanks. I just wanted to have it confirmed.

Regards,

Vikram

HANDLE ERRORS IN SP_XML_PREPAREDOCUMENT

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT an
d
handle any error messages returned.
The problem is, when the procedure reports a parse error my procedure stops.
I can't get my procedure to go on and just report the error. ...
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML code the procedure stops when executing
SP_XML_PREPAREDOCUMENT with an error:
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
Can I prevent MY procedure from stopping when I send bad XML-data to
SP_XML_PREPAREDOCUMENT?Hi
I am guessing you are using SQL Server 2000.
You could try creating a wrapper sproc for the sp_xml_preparedocument, that
returns the handle number, calling it in your outer sproc and handling error
s
generated where your wrapper sproc fails.
The errors will still be thrown but your outer sproc will carry on. A word
of caution - make sure you clean up after it. with sp_xml_removedocument
otherwise you will have trouble with memory getting full of junk XML DOMs.
In fact this may happen anyway with bad XML since you will not get a valid
handle back from sp_xml_preparedocument if it fails and it will have grabbed
some memory. I found the only way to avoid this is to reboot your server
when you start getting memory errors.
Regards
John Harrison
"LakritZtrollet" wrote:

> I'm working on a procedure to import XML-data in a database. First I want
to
> verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT
and
> handle any error messages returned.
> The problem is, when the procedure reports a parse error my procedure stop
s.
> I can't get my procedure to go on and just report the error. ...
> My (simplified) code looks like this:
> CREATE PROCEDURE SP_ADD_XML
> @.XML_XMLSTR TEXT
> AS
> BEGIN
> DECLARE @.FILEHANDLE INT
> DECLARE @.RESULT INT
> EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
> ...
> END
> GO
>
> If I try to parse a bad XML code the procedure stops when executing
> SP_XML_PREPAREDOCUMENT with an error:
> "Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Li
ne
> 2
> XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
> Can I prevent MY procedure from stopping when I send bad XML-data to
> SP_XML_PREPAREDOCUMENT?

Handle Errors In Sp_xml_preparedocument

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT a
nd handle error messages returned.
The problem is, when the procedure reports a parse error the procedure stops
.
I can't get my procedure to go on and just report the error.
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML document the procedure stops when executing SP_X
ML_PREPAREDOCUMENT with an error:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'.
Can I prevent MY procedure from stopping when I send wrong XML-data to SP_XM
L_PREPAREDOCUMENT?Hello LakritZtrollet,
SQL 2000 or 2005?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL2000 SP3
"Kent Tegels" skrev:

> Hello LakritZtrollet,
> SQL 2000 or 2005?
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello LakritZtrollet,
Sorry, no good suggestions here.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/