Retrieving XML from SQL SERVER

Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options '
Can I use a huge template file for representing the whole hierarchy '
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.comIn SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options '
> Can I use a huge template file for representing the whole hierarchy '
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegroups.com...
>|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegroups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>|||Hello Michael,
Sorry it took so long, last w I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Build...l/forxml2k5.asp
> Does that help?
> Best regards
> Michael

Retrieving XML from SQL SERVER

Hi All
I have various tables that have the following hierarchy
TABLE A
TABLE B
TABLE C
TABLE D
TABLE E
TABLE F.
Each of the tables has the right keys to make up the hierarchy, and
each child has one more field than the parent to make the connection.
I would like to pass a parameter to the TABLE A and that should give
back to me the whole xml.I want to call this procedure from an asp.
What are my options ?
Can I use a huge template file for representing the whole hierarchy ?
Please suggest.
Thanks for your time and patience.
Satish
KARRYS@.Gmail.com
In SQL Server 2000 your options are:
XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
component) or writing FOR XML EXPLICIT queries (also lovingly called the
"query from hell" :-)).
In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
EXPLICIT mode).
Best regards
Michael
"Satish KARRY" <karrys@.gmail.com> wrote in message
news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
> Hi All
> I have various tables that have the following hierarchy
> TABLE A
> TABLE B
> TABLE C
> TABLE D
> TABLE E
> TABLE F.
> Each of the tables has the right keys to make up the hierarchy, and
> each child has one more field than the parent to make the connection.
> I would like to pass a parameter to the TABLE A and that should give
> back to me the whole xml.I want to call this procedure from an asp.
> What are my options ?
> Can I use a huge template file for representing the whole hierarchy ?
> Please suggest.
> Thanks for your time and patience.
> Satish
> KARRYS@.Gmail.com
>
|||Hello Michael,
We are exposed to the same challenge, but we are required to use XML
EXPLICIT (caused by political issues)
The only samples I can find are using Customers, Orders & Orderdetails but
not with different types of child elements connected to the same parent
node.
Could you post an example of a query using XML EXPLICIT for the table
structure specified by Satish?
Thanks,
Peter Vervoorn
(if you want to reply directly to me, replace newsuser with my first name)
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2000 your options are:
> XPath queries against annotated XSD schemas (part of the SQLXML mid-tier
> component) or writing FOR XML EXPLICIT queries (also lovingly called the
> "query from hell" :-)).
> In SQL Server 2005, you can also use FOR XML PATH queries (simpler than
> EXPLICIT mode).
> Best regards
> Michael
> "Satish KARRY" <karrys@.gmail.com> wrote in message
> news:1107881623.352032.259820@.g14g2000cwa.googlegr oups.com...
>
|||You may want to look at the SQL Server 2005 whitepaper. It contains FOR XML
EXPLICIT queries that do
TABLE A
TABLE B
TABLE C
The URL is:
http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
Does that help?
Best regards
Michael
"Peter Vervoorn" <newsuser@.vervoorn.com> wrote in message
news:420b640e$0$28986$e4fe514c@.news.xs4all.nl...
> Hello Michael,
> We are exposed to the same challenge, but we are required to use XML
> EXPLICIT (caused by political issues)
> The only samples I can find are using Customers, Orders & Orderdetails but
> not with different types of child elements connected to the same parent
> node.
> Could you post an example of a query using XML EXPLICIT for the table
> structure specified by Satish?
> Thanks,
> Peter Vervoorn
> (if you want to reply directly to me, replace newsuser with my first name)
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OawpNEoDFHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Mike
Would you have an example to the XSD and asp combination in particular.
I am working on sql server 2000 and asp and not asp.net
Thanks for your help.
Sincerely
Satish
|||The documentation and whitepapers on the SQLXML 3.0 component on MSDN or
www.sqlxml.org should have some information to get you started. with
annotated schemas
Best regards
Michael
"A" <karrys@.gmail.com> wrote in message
news:1108990971.486858.152420@.l41g2000cwc.googlegr oups.com...
> Hi Mike
> Would you have an example to the XSD and asp combination in particular.
> I am working on sql server 2000 and asp and not asp.net
> Thanks for your help.
> Sincerely
> Satish
>
|||Hello Michael,
Sorry it took so long, last week I was ill.
The structure shown in the document is what I tried.
For some reason we are now allowed to assemble the XML outside sql, so it's
no issue anymore
Thanks anyway,
Peter
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umZfIvMEFHA.2540@.TK2MSFTNGP09.phx.gbl...
> You may want to look at the SQL Server 2005 whitepaper. It contains FOR
> XML EXPLICIT queries that do
> TABLE A
> TABLE B
> TABLE C
> The URL is:
> http://msdn.microsoft.com/XML/Buildi.../forxml2k5.asp
> Does that help?
> Best regards
> Michael

Retrieving updates made within a transaction?

Hi All,

This seems like a tricky question to me. I have a Stored Procedure
that encapsulates a number of updates to various tables within a
transaction.

However, at a later part of the transaction I need to be able to
select records changed by an update statement made earlier within the
same stored proc (and within the same transaction) and need for that
select to reflect the changed values.

My understanding, however, is that the records aren't actually changed
by the update statement until the transaction is committed, and
therefore my later select statement won't return the expected records
because the update is being held until the transaction is committed.

Is this accurate? And, if so, is there a reasonable workaround that
still leaves me able to rollback the entire transaction if I strike a
problem somewhere along the way?

So, a pseudo code example would be:

BEGIN TRANSACTION

UPDATE mytable SET myid = @.yourid WHERE myid = @.id

SELECT * FROM mytable where myid = @.id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @.id as before the update as after the
update?

Many, many thanks in advance!

Much warmth,

MurrayOn Thu, 01 Apr 2004 16:26:01 GMT, M Wells wrote:

(snip)
>My understanding, however, is that the records aren't actually changed
>by the update statement until the transaction is committed, and
>therefore my later select statement won't return the expected records
>because the update is being held until the transaction is committed.
>Is this accurate?

No. The updates are made. If you rollback the transaction later, the
changes will be undone; the log file is used for this. During the
transaction, you'll get to see the changed data.

Other users (or even you yourself on another connection!) won't see
the changed data until the transaction is committed (*). They won't
see the old data either. The affected rows are locked as soon as they
are hit by an update and this lock will remain until the transaction
is either committed or rolled back.

(*) Exception - you can set a transaction's isolation level to "Read
uncommitted" (aka dirty read). This will cause select statements to
defy any locks and just read the "dirty" data - "dirty", since the
transaction may still be rolled back in which case the reading
transaction has read data that never really existed.

>So, a pseudo code example would be:
>
>BEGIN TRANSACTION
>UPDATE mytable SET myid = @.yourid WHERE myid = @.id
>SELECT * FROM mytable where myid = @.id
>COMMIT TRANSACTION
>In this above example, would the select statement return the records
>that have a myid value of @.id as before the update as after the
>update?

It won't return any rows at all, since the update has just changed to
myid value from @.id to @.yourid.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 01 Apr 2004 21:28:22 +0200, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>>
>>BEGIN TRANSACTION
>>
>>UPDATE mytable SET myid = @.yourid WHERE myid = @.id
>>
>>SELECT * FROM mytable where myid = @.id
>>
>>COMMIT TRANSACTION
>>
>>In this above example, would the select statement return the records
>>that have a myid value of @.id as before the update as after the
>>update?
>It won't return any rows at all, since the update has just changed to
>myid value from @.id to @.yourid.

Hi Hugo,

Thanks for your help! And, laugh, just shows I shouldn't write pseudo
sql before my first cup of coffee.

Thanks again!

Much warmth,

Murray

Retrieving symmetric keys from database.

I've played with various configurations of the MS SQL Server encryption functionality, and come across an embarrassingly easy question that I cannot seem to resolve. How do I retrieve the actual symmetric and asymmetric keys out of the database?

I'd like to explore the possibility of off-loading the encryption/decryption work from the database server to a load-balanced pool of servers. For this model to work the pool would need access to the keys. The symmetric keys currently are generated with the command...

create symmetric key EncryptionKey211

with algorithm = AES_256

ENCRYPTION BY certificate CreditCardCert

Am I missing something obvious here?

Laurentiu wrote an article in his blog that probably can help you:

http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx

Please let us know if it helped and/or if you have further questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks Raul, the link addressed the question quite well. Actually, better than that, because it clarified you cannot retreive the key values from the database for Symmetric or Asymmetric keys... an issue we had had some concerns with.|||

We don’t support backup/load of symmetric keys in SQL Server 2005. If you want to encrypt data both within and outside SQL Server, I would recommend considering using a CLR module.

Let us know if you have further question or if you have any feedback on this feature; we appreciate all your comments.

Thanks a lot,

-Raul Garcia

SDE/T

SQL server Engine

sql

Retrieving data types is slow

Hello,

i'm using SMO to retrieve information from various databases. It works
well except for one thing. When I call the Column.DataType property to
get the SQL type of a column it takes a very long tine. I have a few
databases, each with some tables. There are about 75 columns I think.
If I just browse all columns with SMO and write each name if it not a
system one, it takes about 15 seconds (pretty much anyway). If I also retrieve the data type
it takes about one minute. Any ideas why? The server is a local one.

Thanksstarted reading Michiel's articles. The issue will be fixed shortly I think. Thanks anyway.