September 13, 2009 at 6:08 am
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
cicmt
'
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 6:18 am
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... 😉
September 13, 2009 at 6:21 am
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
September 13, 2009 at 6:21 am
Thank you Lutz,
Thanks a lot.
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 6:32 am
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.
September 14, 2009 at 8:05 am
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
September 14, 2009 at 12:27 pm
At this point I'd like to ask you to do some research.
Here are two possible sources:
http://qa.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...
September 14, 2009 at 3:51 pm
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
September 15, 2009 at 10:46 am
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 😉
September 15, 2009 at 2:41 pm
All good then 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2009 at 1:29 am
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
September 18, 2009 at 1:32 am
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
September 24, 2009 at 1:20 am
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
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply