Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int --document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Retrieving XML data using OpenXML

Hello Everyone:
I have a piece of function that reads through the XML file and updates the
table with the contents.
I am working on to retrieve a specific elemental data, but am not able to do
so.
Below is my Code
CREATE PROCEDURE [dbo].[xmltest]
AS
BEGIN
--Local var for statement header/detail messages
DECLARE @.hDoc int--document handle
DECLARE @.Count int
DECLARE @.errNo int, @.doc nvarchar(4000) , @.Msgid varchar(20)
set @.doc = ' <VendorMasterData>
<VendorInfo>
<MessageId type="A">0000000018089158</MessageId>
<Date>2005-12-07</Date><Time zone="PST">05:02:31.000</Time>
<MessageType>C</MessageType>
<Sort type="SORT1">ABC</Sort>
<Sort type="SORT2">XYZ</Sort>
</VendorInfo>
</VendorMasterData>'
--Get the XML doc handle
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.doc
IF @.@.ERROR <> 0
BEGIN
return @.@.ERROR
END
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo',3) WITH
([Sort] varchar(30),type varchar(30))
EXEC sp_xml_removedocument @.hdoc
RETURN (0)
END
GO
I would like to retrieve both the values of Sort (both Sort1 and Sort2
types). How can I do that. Right now I am able to retrieve only 1 value.
Thanks for you help.
Regards/Shriram.
Hello shriram2977,

> I have a piece of function that reads through the XML file and updates
> the table with the contents.
> I would like to retrieve both the values of Sort (both Sort1 and Sort2
> types). How can I do that. Right now I am able to retrieve only 1
> value.
Does this give you what you were looking for?
SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
([Sort] varchar(30) 'text()',type varchar(30) '@.type')
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Kent Tegels, Thanks much. It does.
Jus curious, what is this text() and where can you use them in OpenXML.
Thanks/Shriram.
"Kent Tegels" wrote:

> Hello shriram2977,
>
> Does this give you what you were looking for?
> SELECT * FROM OPENXML(@.hdoc, '/VendorMasterData/VendorInfo/Sort',2) WITH
> ([Sort] varchar(30) 'text()',type varchar(30) '@.type')
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello shriram2977,
text() is an xpath function that returns the lexical value of an element's
inner-text. You can use it (and some other functions) as what's known as
a metaproprety. This is covered in Books-On-Line.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

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