Localisation using custom assemblies

Hi,
Further to my post on May 31 on the same subject, we are still facing a
permissions problem with loading satellite resource assemblies from a
custom assembly.
We have done all that is recommended by the Bryant rs web site, and
still unable to proceed.
To reiterate the problem:
We need to enable label translation for our clients through the use of
.net resource assemblies; the resource assemblies must remain separate.
We developed a custom assembly so that in the report it is possible to
call a method with a culture name and resource id, and the
corresponding resource is to be returned in the culture specified.
We have tested that in a local pc scenario, it works perfectly. It
does not work when it is executed remotely - only the neutral
resources are returned.
We have made the appropriate permissions assertion and code group
declaration, but is unable to proceed further. Any help will be very
much appreciated.
Michael Cheng: I have a test case, if you don't mind I can forward the
test case in a zip file to your email address...
Desperate,
Siew FaiHello Siew,
To understand the issue better, I'd like to know how you execute it
remotely. Will you describe it in more details? What is the exact error
message you encounter?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Siew Fai" <siewfai.hoy@.gmail.com>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Localisation using custom assemblies
| Date: 14 Aug 2005 19:22:27 -0700
| Organization: http://groups.google.com
| Lines: 31
| Message-ID: <1124072547.541100.236230@.g47g2000cwa.googlegroups.com>
| NNTP-Posting-Host: 203.222.174.236
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1124072553 17754 127.0.0.1 (15 Aug 2005
02:22:33 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Mon, 15 Aug 2005 02:22:33 +0000 (UTC)
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g47g2000cwa.googlegroups.com;
posting-host=203.222.174.236;
| posting-account=W-T95Q0AAABWrZAGzmtXCMOc3JBrVGnv
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g47g2000cwa.googlegroups.com!not-fo
r-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50341
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
| Further to my post on May 31 on the same subject, we are still facing a
| permissions problem with loading satellite resource assemblies from a
| custom assembly.
|
| We have done all that is recommended by the Bryant rs web site, and
| still unable to proceed.
|
| To reiterate the problem:
| We need to enable label translation for our clients through the use of
| .net resource assemblies; the resource assemblies must remain separate.
| We developed a custom assembly so that in the report it is possible to
| call a method with a culture name and resource id, and the
| corresponding resource is to be returned in the culture specified.
|
| We have tested that in a local pc scenario, it works perfectly. It
| does not work when it is executed remotely - only the neutral
| resources are returned.
|
| We have made the appropriate permissions assertion and code group
| declaration, but is unable to proceed further. Any help will be very
| much appreciated.
|
| Michael Cheng: I have a test case, if you don't mind I can forward the
| test case in a zip file to your email address...
|
|
| Desperate,
| Siew Fai
|
||||Of course, I couldn't send because your email address is not available.
Siew Fai.|||Hi,
Thanks for the prompt reply.
I guess I used the term a little bit too loosely. Suppose there is a
box, let's name it "devsql01" that has all the reporting services
component installed (designer, manager and server). The custom
assembly and the resources are installed in the server bin folder and
the server policy file configured.
I have a client pc that requested the a report either via the url or
the webservice, or even the report manager that will use the services
of the custom assembly, which is to display label in the specified
culture. In this case, only the neutral resources are displayed, this
is incorrect.
If I do the same on the "devsql01" box itself, the correct resources
are displayed. This, I believe, is executing in the "MyComputer" zone
isn't it?
I do have a zip file containing all the source files with testing
instructions (no binaries) I can forward to illustrate the problem
clearly, if you would just send me an email, I can forward that on.
Please help,
Siew Fai.|||Hello Siew,
I have reproduced the issue on my side. If I log on as a domain user
without local admin right on report server, I recevied the following error:
The permissions granted to user 'domain\username' are insufficient for
performing this operation. (rsAccessDenied)
If I added the domain\username to the local admin groups on report server
machine, the issue went away.
I think this is expected behavior because for Report Manager it uses
"impersonate=true" configuration in web.config file. Any client users try
to access a report, report manager impersonte the identity of this user
when performing operation such as report execution. If the user does not
have the proper permission on report server, the permission exception will
be thrown.
If you do not want this behavior, you may consider use impersontion inside
the custom assembly so that it can execute as a identity with local admin
rights. I have included the following article for your reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemSecurityPrincipalWindowsIdentityClassImpersonateTopic2.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Siew Fai" <siewfai.hoy@.gmail.com>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Re: Localisation using custom assemblies
| Date: 15 Aug 2005 03:14:05 -0700
| Organization: http://groups.google.com
| Lines: 28
| Message-ID: <1124100845.207984.265700@.g43g2000cwa.googlegroups.com>
| References: <1124072547.541100.236230@.g47g2000cwa.googlegroups.com>
| <262SSYXoFHA.3120@.TK2MSFTNGXA01.phx.gbl>
| NNTP-Posting-Host: 203.166.246.91
| Mime-Version: 1.0
| Content-Type: text/plain; charset="iso-8859-1"
| X-Trace: posting.google.com 1124100850 30123 127.0.0.1 (15 Aug 2005
10:14:10 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Mon, 15 Aug 2005 10:14:10 +0000 (UTC)
| In-Reply-To: <262SSYXoFHA.3120@.TK2MSFTNGXA01.phx.gbl>
| User-Agent: G2/0.2
| Complaints-To: groups-abuse@.google.com
| Injection-Info: g43g2000cwa.googlegroups.com; posting-host=203.166.246.91;
| posting-account=W-T95Q0AAABWrZAGzmtXCMOc3JBrVGnv
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!nntp.giganews.com!nx02.iad01.newshosting

loading/storing image from ms sql by using vb6

how to loading message from the ms sql by using vb 6. please show me the vb6 coding.Kmok, do a search of this forum. I think I've seen VB code posted for this a few months back.

blindman

Loading XML to SLQ Server

Whats the best way to load XML into SQL Server..
For example just a single XML file..
thanks
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
A previous post to the newsgroup:
"In SQL Server 2000 you have two main options - using the OPENXML technology
or using the SQLXML BulkLoad technology. Choosing between these two options
is largely a matter of personal choice in terms of what technologies you
feel most comfortable with. OPENXML is a technology that runs in the server
while and requires a reasonable level of familiarity with T-SQL. SQLXML
BulkLoad runs on the client and requires no T-SQL but you need to generate
an Annotated XSD to determine the mapping between the XML and the database.
One other thing to consider is the size of the data being loaded. OPENXML
loads the entire XML into memory on the server so if you are loading large
amounts of data you may want to go with a BulkLoad solution.
You can get more information on OPENXML in SQL BOL or online at
http://msdn.microsoft.com/library/de...oa-oz_5c89.asp
SQLXML documentation is at
http://msdn.microsoft.com/library/de...nch_SQLXML.asp
or you can check out http://www.sqlxml.org/ for some extra info on these
technologies."
I should also add that if you want to develop your application by using
..Net, you may want to use Dataset as well. Dataset has also a drawback of
loading all data into memory similar to OpenXml.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OKM$bJGoEHA.4068@.tk2msftngp13.phx.gbl...
> Whats the best way to load XML into SQL Server..
> For example just a single XML file..
> thanks
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

loading xml into SQL 2000

I have some xml files that I want to load into SQL tables. I can use OPENXML and create the table fields and load the document just fine. BUT, is there a way to 1) call the location of the xml document rather than paste all the code and 2) have the table created for me? Below is a SQL example. LMK what you think.

Declare @.idoc int
Declare @.xmldoc varchar (8000)

set @.xmldoc = '
<?xml version="1.0" encoding="utf-8"?>
<ROOT>
<general>
<full_destination_name>London</full_destination_name>
<intro_mini>

London''s contrasts and cacophonies both infuriate and seduce.

</intro_mini>
<intro_short>

London - the grand resonance of its very name suggests history and might. Its opportunities for entertainment by day and night go on and on and on. It''s a city that exhilarates and intimidates, stimulates and irritates in equal measure, a grubby Monopoly board studded with stellar sights.

</intro_short>
<intro_medium>

It''s a cosmopolitan mix of Third and First Worlds, chauffeurs and beggars, the stubbornly traditional and the proudly avant-garde. But somehow - between ''er Majesty and Boy George, Damien Hirst and JMW Turner, Bow Bells and Big Ben - it all hangs together.

</intro_medium>
<intro_quote>

''When a man is tired of London, he is tired of life; for there is in London all that life can afford.'' - Samuel Johnson

</intro_quote>
<timezones>
<timezone>
<gmt_utc>0</gmt_utc>
<timezone_name>Greenwich Mean Time</timezone_name>
</timezone>
</timezones>
<daylight_savings_start>last Sunday in March</daylight_savings_start>
<daylight_savings_end>last Sunday in October</daylight_savings_end>
</general>
</ROOT>'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmldoc

INSERT INTO General

SELECT * FROM OPENXML(@.idoc, 'ROOT/general',3)
WITH General

EXEC master.dbo.sp_xml_removedocument @.idoc

actually, now I load the xml into MS Visual Studio and create the xsd from there. I then load it into a VB Script and successfully execute a DTS package. Question is, where is the table, data?

Here's the xsd file:

<?xml version="1.0" ?>

<xschema id="events" targetNamespace="http://tempuri.org/events.xsd" xmlns:mstns="http://tempuri.org/events.xsd"

xmlns="http://tempuri.org/events.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata"

attributeFormDefault="qualified" elementFormDefault="qualified">

<xs:element name="events" msdata:IsDataSet="true" msdata:EnforceConstraints="False">

<xs:complexType>

<xs:choice maxOccurs="unbounded">

<xs:element name="event">

<xs:complexType>

<xsequence>

<xs:element name="event_name" type="xstring" minOccurs="0" />

<xs:element name="event_from_date" type="xstring" minOccurs="0" />

<xs:element name="event_type" nillable="true" minOccurs="0" maxOccurs="unbounded">

<xs:complexType>

<xsimpleContent msdata:ColumnName="event_type_Text" msdata:Ordinal="1">

<xs:extension base="xstring">

<xs:attribute name="node_id" form="unqualified" type="xstring" />

</xs:extension>

</xsimpleContent>

</xs:complexType>

</xs:element>

</xsequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xschema>

and here's the VB Script:

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success
Dim objXBulkLoad
Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=SQL-DEV;UID=;PWD=;DATABASE=SQLDBA;"

objXBulkLoad.KeepIdentity = False

'Optional Settings
objXBulkLoad.ErrorLogFile = "c:\temp\NWError.LOG"
objXBulkLoad.TempFilePath = "c:\temp"

'Executing the bulk-load
objXBulkLoad.Execute "C:\TEMP\xml\test_xml\events.xsd", "C:\TEMP\xml\test_xml\events.xml"

Main = DTSTaskExecResult_Success
End Function

shouldn't there now be an events and events and event_type table present?

loading XML from SQL Server -- faster

I am using a text column to store XML data on a sql server table.
The data contained in these XML documents maps to a few different
schemas but they share a subset of tags which I use to query with
sp_xml_preparedocument and openxml.
The problem I'm having is that this process is very slow: for ~2500
xml documents, it takes 12 seconds to run.
Is there a way to query these XML documents faster using SQL Server?
Or I will have to reduce my working set of records?
Here is the code I've written:
CREATE PROCEDURE dbo.QueryDocuments
AS
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.namespacedef varchar(512)
declare @.rowpattern varchar(128)
declare @.id uniqueidentifier
declare @.doctype varchar(50)
declare @.docinfo varchar(128)
declare @.docversion varchar(3)
declare @.tstamp datetime
declare @.lastchange varchar(50)
IF OBJECT_ID('tempdb..#rs') IS NOT NULL
DROP TABLE #rs
create table #rs (id uniqueidentifier, doctype
varchar(50), docinfo varchar(128), docversion
varchar(10), tstamp datetime, lastchange varchar(50),
cliente varchar(128), estacao varchar(128), tecnico
varchar(256), inicio datetime)
declare crs cursor for
select Documentos.id,
Documentos.doctype,
DocumentTypes.docinfo,
Documentos.docversion,
Documentos.tstamp,
Documentos.lastchange
from
Documentos
inner join
DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
Documentos.docversion = DocumentTypes.docversion
declare @.cmd varchar(8000)
declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
)
open crs
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
while @.@.fetch_status = 0
begin
declare @.idoc int
set @.namespacedef = (select
substring(convert(varchar(8000), xmldata),
patindex('%<my:%', xmldata), patindex('%>%',
substring(xmldata, patindex('%<my:%', xmldata),
500))-1) from Documentos where [id] = @.id) +
'/>'
set @.rowpattern = '/' + substring(@.namespacedef, 2,
charindex(' ', @.namespacedef)-1)
select @.xml_0 = replace(substring( xmldata, ( 0*7000
) + 1, 7000 ), char(39),
char(39)+char(39) ),
@.xml_1 = replace(substring( xmldata, ( 1*7000
) + 1, 7000 ), char(39),
char(39)+char(39) )
from Documentos where [id] = @.id
exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
+ ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
@.idoc')
open he_cur
fetch he_cur into @.idoc
deallocate he_cur
insert into #rs
select
@.id,
@.doctype,
@.docinfo,
@.docversion,
@.tstamp,
@.lastchange,
*
from openxml(@.idoc, @.rowpattern, 2)
with (
cliente varchar(128) 'my:Cliente',
estacao varchar(128) 'my:Estacao',
tecnico varchar(256) 'my:Tecnico',
inicio datetime 'my:Inicio'
)
exec sp_xml_removedocument @.idoc
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
end
close crs
deallocate crs
select *
from #rs
order by
year(inicio) asc,
month(inicio) asc,
day(inicio) asc
drop table #rs
GO
Thanks for your help.Moving to SQL Server 2005 could help, since you would not need to do the
string manipulations.
Do you know where you use the time? In the string ops, XML parser or OpenXML
calls?
Also, could you just pass the @.xml_* for the namespace declarations? Or use
a constant namespace declaration?
Best regards
Michael
"mgcm" <miltonmoura@.gmail-dot-com.no-spam.invalid> wrote in message
news:CsWdneQdj-phirveRVn_vA@.giganews.com...
>I am using a text column to store XML data on a sql server table.
> The data contained in these XML documents maps to a few different
> schemas but they share a subset of tags which I use to query with
> sp_xml_preparedocument and openxml.
> The problem I'm having is that this process is very slow: for ~2500
> xml documents, it takes 12 seconds to run.
> Is there a way to query these XML documents faster using SQL Server?
> Or I will have to reduce my working set of records?
> Here is the code I've written:
>
> CREATE PROCEDURE dbo.QueryDocuments
> AS
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.namespacedef varchar(512)
> declare @.rowpattern varchar(128)
> declare @.id uniqueidentifier
> declare @.doctype varchar(50)
> declare @.docinfo varchar(128)
> declare @.docversion varchar(3)
> declare @.tstamp datetime
> declare @.lastchange varchar(50)
> IF OBJECT_ID('tempdb..#rs') IS NOT NULL
> DROP TABLE #rs
> create table #rs (id uniqueidentifier, doctype
> varchar(50), docinfo varchar(128), docversion
> varchar(10), tstamp datetime, lastchange varchar(50),
> cliente varchar(128), estacao varchar(128), tecnico
> varchar(256), inicio datetime)
> declare crs cursor for
> select Documentos.id,
> Documentos.doctype,
> DocumentTypes.docinfo,
> Documentos.docversion,
> Documentos.tstamp,
> Documentos.lastchange
> from
> Documentos
> inner join
> DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
> Documentos.docversion = DocumentTypes.docversion
> declare @.cmd varchar(8000)
> declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
> )
> open crs
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> while @.@.fetch_status = 0
> begin
> declare @.idoc int
> set @.namespacedef = (select
> substring(convert(varchar(8000), xmldata),
> patindex('%<my:%', xmldata), patindex('%>%',
> substring(xmldata, patindex('%<my:%', xmldata),
> 500))-1) from Documentos where [id] = @.id) +
> '/>'
> set @.rowpattern = '/' + substring(@.namespacedef, 2,
> charindex(' ', @.namespacedef)-1)
> select @.xml_0 = replace(substring( xmldata, ( 0*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) ),
> @.xml_1 = replace(substring( xmldata, ( 1*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) )
> from Documentos where [id] = @.id
> exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
> OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
> + ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
> @.idoc')
> open he_cur
> fetch he_cur into @.idoc
> deallocate he_cur
> insert into #rs
> select
> @.id,
> @.doctype,
> @.docinfo,
> @.docversion,
> @.tstamp,
> @.lastchange,
> *
> from openxml(@.idoc, @.rowpattern, 2)
> with (
> cliente varchar(128) 'my:Cliente',
> estacao varchar(128) 'my:Estacao',
> tecnico varchar(256) 'my:Tecnico',
> inicio datetime 'my:Inicio'
> )
> exec sp_xml_removedocument @.idoc
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> end
> close crs
> deallocate crs
> select *
> from #rs
> order by
> year(inicio) asc,
> month(inicio) asc,
> day(inicio) asc
> drop table #rs
> GO
> Thanks for your help.
>

loading XML from SQL Server -- faster

I am using a text column to store XML data on a sql server table.
The data contained in these XML documents maps to a few different
schemas but they share a subset of tags which I use to query with
sp_xml_preparedocument and openxml.
The problem I'm having is that this process is very slow: for ~2500
xml documents, it takes 12 seconds to run.
Is there a way to query these XML documents faster using SQL Server?
Or I will have to reduce my working set of records?
Here is the code I've written:
CREATE PROCEDURE dbo.QueryDocuments
AS
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.namespacedef varchar(512)
declare @.rowpattern varchar(128)
declare @.id uniqueidentifier
declare @.doctype varchar(50)
declare @.docinfo varchar(128)
declare @.docversion varchar(3)
declare @.tstamp datetime
declare @.lastchange varchar(50)
IF OBJECT_ID('tempdb..#rs') IS NOT NULL
DROP TABLE #rs
create table #rs (id uniqueidentifier, doctype
varchar(50), docinfo varchar(128), docversion
varchar(10), tstamp datetime, lastchange varchar(50),
cliente varchar(128), estacao varchar(128), tecnico
varchar(256), inicio datetime)
declare crs cursor for
selectDocumentos.id,
Documentos.doctype,
DocumentTypes.docinfo,
Documentos.docversion,
Documentos.tstamp,
Documentos.lastchange
from
Documentos
inner join
DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
Documentos.docversion = DocumentTypes.docversion
declare @.cmd varchar(8000)
declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
)
open crs
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
while @.@.fetch_status = 0
begin
declare @.idoc int
set @.namespacedef = (select
substring(convert(varchar(8000), xmldata),
patindex('%<my:%', xmldata), patindex('%>%',
substring(xmldata, patindex('%<my:%', xmldata),
500))-1) from Documentos where [id] = @.id) +
'/>'
set @.rowpattern = '/' + substring(@.namespacedef, 2,
charindex(' ', @.namespacedef)-1)
select @.xml_0 = replace(substring( xmldata, ( 0*7000
) + 1, 7000 ), char(39),
char(39)+char(39) ),
@.xml_1 = replace(substring( xmldata, ( 1*7000
) + 1, 7000 ), char(39),
char(39)+char(39) )
from Documentos where [id] = @.id
exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
+ ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
@.idoc')
open he_cur
fetch he_cur into @.idoc
deallocate he_cur
insert into #rs
select
@.id,
@.doctype,
@.docinfo,
@.docversion,
@.tstamp,
@.lastchange,
*
fromopenxml(@.idoc, @.rowpattern, 2)
with (
cliente varchar(128)'my:Cliente',
estacao varchar(128)'my:Estacao',
tecnico varchar(256)'my:Tecnico',
iniciodatetime'my:Inicio'
)
exec sp_xml_removedocument @.idoc
fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
@.tstamp, @.lastchange
end
close crs
deallocate crs
select*
from#rs
order by
year(inicio) asc,
month(inicio) asc,
day(inicio) asc
drop table #rs
GO
Thanks for your help.
Moving to SQL Server 2005 could help, since you would not need to do the
string manipulations.
Do you know where you use the time? In the string ops, XML parser or OpenXML
calls?
Also, could you just pass the @.xml_* for the namespace declarations? Or use
a constant namespace declaration?
Best regards
Michael
"mgcm" <miltonmoura@.gmail-dot-com.no-spam.invalid> wrote in message
news:CsWdneQdj-phirveRVn_vA@.giganews.com...
>I am using a text column to store XML data on a sql server table.
> The data contained in these XML documents maps to a few different
> schemas but they share a subset of tags which I use to query with
> sp_xml_preparedocument and openxml.
> The problem I'm having is that this process is very slow: for ~2500
> xml documents, it takes 12 seconds to run.
> Is there a way to query these XML documents faster using SQL Server?
> Or I will have to reduce my working set of records?
> Here is the code I've written:
>
> CREATE PROCEDURE dbo.QueryDocuments
> AS
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.namespacedef varchar(512)
> declare @.rowpattern varchar(128)
> declare @.id uniqueidentifier
> declare @.doctype varchar(50)
> declare @.docinfo varchar(128)
> declare @.docversion varchar(3)
> declare @.tstamp datetime
> declare @.lastchange varchar(50)
> IF OBJECT_ID('tempdb..#rs') IS NOT NULL
> DROP TABLE #rs
> create table #rs (id uniqueidentifier, doctype
> varchar(50), docinfo varchar(128), docversion
> varchar(10), tstamp datetime, lastchange varchar(50),
> cliente varchar(128), estacao varchar(128), tecnico
> varchar(256), inicio datetime)
> declare crs cursor for
> select Documentos.id,
> Documentos.doctype,
> DocumentTypes.docinfo,
> Documentos.docversion,
> Documentos.tstamp,
> Documentos.lastchange
> from
> Documentos
> inner join
> DocumentTypes on Documentos.doctype = DocumentTypes.doctype and
> Documentos.docversion = DocumentTypes.docversion
> declare @.cmd varchar(8000)
> declare @.xml_0 varchar( 8000 ), @.xml_1 varchar( 8000
> )
> open crs
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> while @.@.fetch_status = 0
> begin
> declare @.idoc int
> set @.namespacedef = (select
> substring(convert(varchar(8000), xmldata),
> patindex('%<my:%', xmldata), patindex('%>%',
> substring(xmldata, patindex('%<my:%', xmldata),
> 500))-1) from Documentos where [id] = @.id) +
> '/>'
> set @.rowpattern = '/' + substring(@.namespacedef, 2,
> charindex(' ', @.namespacedef)-1)
> select @.xml_0 = replace(substring( xmldata, ( 0*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) ),
> @.xml_1 = replace(substring( xmldata, ( 1*7000
> ) + 1, 7000 ), char(39),
> char(39)+char(39) )
> from Documentos where [id] = @.id
> exec ('declare @.idoc int;exec sp_xml_preparedocument @.idoc
> OUTPUT, ''' + @.xml_0 + @.xml_1 /*+ @.xml_2 + @.xml_3 + @.xml_4 + @.xml_5*/
> + ''', ''' + @.namespacedef + ''';declare he_cur cursor forselect
> @.idoc')
> open he_cur
> fetch he_cur into @.idoc
> deallocate he_cur
> insert into #rs
> select
> @.id,
> @.doctype,
> @.docinfo,
> @.docversion,
> @.tstamp,
> @.lastchange,
> *
> from openxml(@.idoc, @.rowpattern, 2)
> with (
> cliente varchar(128) 'my:Cliente',
> estacao varchar(128) 'my:Estacao',
> tecnico varchar(256) 'my:Tecnico',
> inicio datetime 'my:Inicio'
> )
> exec sp_xml_removedocument @.idoc
> fetch next from crs into @.id, @.doctype, @.docinfo, @.docversion,
> @.tstamp, @.lastchange
> end
> close crs
> deallocate crs
> select *
> from #rs
> order by
> year(inicio) asc,
> month(inicio) asc,
> day(inicio) asc
> drop table #rs
> GO
> Thanks for your help.
>

loading xml file with sp_xml_preparedocument

is it possible to load a file containing xml data with
sp_xml_preparedocument.
In SQL Server 2000: Yes, if you read the file in an ADO/OLEDB program and
pass the content to a stored proc.
In SQL Server 2005: Yes, use OpenROWSET(BULK)
Best regards
Michael
"ed" <anonymous@.discussions.microsoft.com> wrote in message
news:990d01c433c3$ffe2f600$a101280a@.phx.gbl...
> is it possible to load a file containing xml data with
> sp_xml_preparedocument.

Loading XML data using DTS.

I am trying to load XML data into SQL Server 2000/sp3a. Since the XML document is in a file, I used DTS to first load the data into a temporary file (with a text field, used varchar(5000) as well) and then copy it into a variable for use in sp_xml_prepare
document.
Everything works fine except that of the date field. For instance, I have defined it as
<ReceivedDate>2004-03-29</ReceivedDate>
However, once it is loaded into the temporary table it is saved as
<ReceivedDate>2004-29-03</ReceivedDate>
I think I am missing something. Please help.
Thanks,
MZeeshan
I'm new at XML, but what I've played with so far, SQL does not create a
root element for your xml file. It looks like it's using the first
record's properties/child elements to name your table?
Robert
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Well... I ve created a table myself for getting the parsed data. I am getting that through SQL server provided T-SQL utility OPENXML. That is working fine. However, there is some problem in transformation.
|||This is unrelated to the DTS question.
You can add a root element using the root node property of ADO/OLEDB/URL
queries, use an explicit mode query to add it or (only available in the
upcoming SQL Server 2005) use the ROOT directive on a FOR XML clause.
Best regards
Michael
"Robert Taylor" <anonymous@.devdex.com> wrote in message
news:ejkj$lpQEHA.1440@.TK2MSFTNGP10.phx.gbl...
> I'm new at XML, but what I've played with so far, SQL does not create a
> root element for your xml file. It looks like it's using the first
> record's properties/child elements to name your table?
> Robert
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!