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 ntext column value skips values.

I have the unfortunate task of dealing with an ntext column. I have to updat
e
part of the contents but first I was just trying to display the contents in
Query Analyzer using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else. Does anyone have a source for useful info in dealing with
ntext?
thanks,
MichaelSnake wrote:
> I have the unfortunate task of dealing with an ntext column. I have
> to update part of the contents but first I was just trying to display
> the contents in Query Analyzer using a script from page 61-62 of the
> Guru's transact sql book. Well, the script does print out a few
> characters, skip a few, print a few, skip a few, . . . It appears
> that accessing an ntext column is quite different than accessing a
> text column. BOL is not very helpful on this. Also, READTEXT only
> displays a few characters at a time no matter what the chunk size is
> set to, so I can't tell if the problem is Query analyzer or something
> else. Does anyone have a source for useful info in dealing with
> ntext?
> thanks,
> Michael
Can you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.
I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.
create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go
ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD FOREIGN KEY
(
[pub_id]
) REFERENCES [publishers] (
[pub_id]
)
GO
insert into pub_info2 select * from pub_info
go
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(pr_info)
FROM pub_info2 pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
select @.ptrval
READTEXT pub_info2.pr_info @.ptrval 0 25
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com|||>I have the unfortunate task of dealing with an ntext column. I have to
>update
> part of the contents but first I was just trying to display the contents
> in
> Query Analyzer
See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.
I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):
CREATE TABLE data
(
id INT UNIQUE,
txt TEXT
)
GO
SET NOCOUNT ON
DECLARE @.foo NVARCHAR(4000)
SELECT @.foo = REPLICATE('a',4000)
-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@.foo+@.foo+@.foo+@.foo+@.foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'
DECLARE
@.dLen INT,
@.nRows INT,
@.i INT,
@.rowID INT,
@.curLine NVARCHAR(4000)
SET @.rowID = 1 -- change this to see the other result
SELECT
@.i = 0,
@.dLen = DATALENGTH(txt),
@.nRows = (@.dLen / 4000) + 1
FROM data WHERE id=@.rowID
WHILE @.i < @.nRows
BEGIN
SELECT @.curLine = SUBSTRING(txt, (4000*@.i)+1, 4000) FROM data WHERE id =
@.rowID
PRINT @.curLine
SET @.i = @.i + 1
END
DROP TABLE data

Retrieving data in a Multiple Record Scenario

I have table "student" and it has 3 fields "Id", "Name" and "JoinDt".

Its contents are given below

Id Name JoinDt

1 One 1/1/2005

2 Two 2/2/2006

3 Three 3/3/2007

When I tried to execute the following query

declare @.Id int
declare @.Name varchar(50)

Select @.Id =Id, @.Name=Name From student

Where JoinDt>'1/1/2006' Order By JoinDt

Select @.Id ,@.Name

Iam getting the result like

3 Three

That means the query returns the value of last record in that cursor.

My doubt is whether this sort of queries will provide consistent results or not.

Yes the above query always give the last record.

First it will assign the 1 row value, then 2 row value ..... finally it will assign the last row value (somthing like loop).

|||

The simple & faster version of query ...

Select Top 1 @.Id =Id, @.Name=Name From student

Where JoinDt>'1/1/2006' Order By JoinDt Desc

Select @.Id ,@.Name

|||

If you are using @.ID and @.Name for display the out put then TSQL Look like,

Select Top 1 Id, Name From student

Where JoinDt>'1/1/2006' Order By JoinDt Desc

Regards

manoj

|||Thank U It worked!!!