Advice on XML data Variable declaring, Inserting, Reading, Deleting ??

Question

Post reply

Advice on XML data Variable declaring, Inserting, Reading, Deleting ??

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 1

PLease help me in this, I am getting an error like while executing the following query

"XQuery [value()]: No more tokens expected at the end of the XQuery expression. Found 'AdminDetails'."

CREATE TABLE DETAILS

(

EMPNAME VARCHAR(50),

EMPNTId VARCHAR(50),

CREATEDBY VARCHAR(20),

EMPMAILId VARCHAR(50)

)

GO

CREATE PROCEDURE InsertUserEmpDetails

@XmlData XML

AS

BEGIN

DECLARE @Table

TABLE (

EmpName VARCHAR(50),

EmpNtId VARCHAR(50),

CreatedBy VARCHAR(20),

EmpMailId VARCHAR(50)

);

INSERT @Table

(

EmpName,

EmpNtId,

CreatedBy,

EmpMailId

)

SELECT Nodes.XmlData.value('.AdminDetails/Name[1]', 'varchar(50)'),

Nodes.XmlData.value('.AdminDetails/EmpNtId[1]', 'varchar(30)'),

Nodes.XmlData.value('.AdminDetails/CreatedBy[1]', 'varchar(30)'),

Nodes.XmlData.value('.AdminDetails/EmpmailId[1]', 'varchar(20)')

FROM @XmlData.nodes('XmlData') Nodes (XmlData);

SELECT Name,

EmpNtId,

CreateBy,

EmpmailId

FROM @Table;

END

exec InsertUserEmpDetails

'

Venu

kshevg

cicmt

'

Venu Gopal.K
Software Engineer
INDIA

LutzM SSC Guru Points: 107049 More actions · Answer 2

Change your select clause to

SELECT c.value('Name[1]', 'varchar(50)'),

c.value('EmpNtId[1]', 'varchar(30)'),

c.value('CreateBy[1]', 'varchar(30)'),

c.value('EmpmailId[1]', 'varchar(20)')

FROM @XmlData.nodes('XmlData/AdminDetails') T(c);

Also, you should decide whether to use [CreateBy] or [CreatedBy]... It's less confusing... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

I imagine it needs to be ./AdminDetails not .AdminDetails (note the forward slash)

Have you done much XQuery?

edit: Lutz's rewrite also works of course!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 4

Thank you Lutz,

Thanks a lot.

Venu Gopal.K
Software Engineer
INDIA

LutzM SSC Guru Points: 107049 More actions · Answer 5

venu_ksheerasagaram (9/13/2009)
Thank you Lutz,
Thanks a lot.

You're very welcome. 🙂

If you'd like to (or have to) look deeper into XQuery I'd like to redirect you to one of my previous posts, where I mentioned a series of great xml articles. You should take the time to look into it.

http://www.sqlservercentral.com/Forums/FindPost786896.aspx

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 6

Hi Friends,

The advice which you given are very helpful.

Here is one more query, Please help me in this

There is a table named Workers as below

WorkerID----- WorkerName-----workerAge------WorkerGroup

----1------------abc-------------21---------------dep1

----2------------sds-------------25---------------dep2

----3------------asd-------------27---------------dep3

----4------------hgf-------------23---------------dep4

while selecting the column in XML output i am using the following script

select WORKERNAME from workers for xml path

the output for the above query is as follows:

abc

sds

asd

hgf

but i need the output as below:

abc

sds

asd

hgf

Please help me in this,

Thanks,

Venu Gopal.K
Software Engineer
INDIA

LutzM SSC Guru Points: 107049 More actions · Answer 7

At this point I'd like to ask you to do some research.

Here are two possible sources:

http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/

and

BOL, section "FOR XML clause".

To play around with the various options of FOR XML is no complex science. It may just take a moment. But as a minimum you'll learn more options than you would by copying one of our solutions...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 8

select WORKERNAME from workers for xml path(''), ROOT('row');

edit: Sorry Lutz - I missed your post :blush:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 9

Paul White (9/14/2009)
edit: Sorry Lutz - I missed your post :blush:

Nothing to be sorry for!!

When looking at the OP's last login time I'd expect he's following my advice, 'cause I don't think he recognized your solution yet.

If he gets back, he's got something to compare his solution to.

I can't find anything wrong with that 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 10

All good then 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 11

Hi Paul,Lutz and All,

Please help me in below:

I wrote a stored procedure ad passing Executing the SP as follows, please correct me in this becuase i am not able to get it.

Stored Procedure:

ALTER PROCEDURE SP_Save_And_Close

(

@XMLIP XML

)

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION T7

DECLARE @TEMPTABLE

TABLE

(

FIELD VARCHAR(50)

);

INSERT @TEMPTABLE

(

FIELD

)

SELECT Nodes.XmlData.value('./Row[1]', 'varchar(50)') FROM @XMLIP.nodes('Rows') Nodes (XmlData)

COMMIT TRANSACTION T7;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

ROLLBACK TRANSACTION T6;

END CATCH

END

Execition of the Stored procedure:

EXEC SP_Save_And_Close

'

ABCD

EFGH

IJKL

MNOP

'

By this i am getting only the first row, pls let me knowhow to get all the elements present in the XML data.Because the number of elements present in the XML data will vary as per requirement.

Thaks in advance,

Venu Gopal.K
Software Engineer
INDIA

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 12

Hi Paul,Lutz and All,

Please help me in below:

I wrote a stored procedure ad passing Executing the SP as follows, please correct me in this becuase i am not able to get it.

Stored Procedure:

ALTER PROCEDURE SP_Save_And_Close

(

@XMLIP XML

)

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION T7

DECLARE @TEMPTABLE

TABLE

(

FIELD VARCHAR(50)

);

INSERT @TEMPTABLE

(

FIELD

)

SELECT Nodes.XmlData.value('./Row[1]', 'varchar(50)') FROM @XMLIP.nodes('Rows') Nodes (XmlData)

COMMIT TRANSACTION T7;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

ROLLBACK TRANSACTION T6;

END CATCH

END

Execition of the Stored procedure:

EXEC SP_Save_And_Close

'

ABCD

EFGH

IJKL

MNOP

'

By this i am getting only the first row, pls let me knowhow to get all the elements present in the XML data.Because the number of elements present in the XML data will vary as per requirement.

Thaks in advance,

Venu Gopal.K
Software Engineer
INDIA

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 13

i Friends,

I am facing a problem in writing a stored procedure for searching entire database(All tables) when a word is given as input.

Please provide me your valuable suggestions. and the ways of the stored procedure to write.

Thank you in advance,

Venu Gopal.K
Software Engineer
INDIA