September 12, 2009 at 6:16 am
Hi Friends,
I am new to using XML in SQL Server 2005, i am facing some doubts , please clarify me:
1) How to insert a data in XML format in SQL Server 2005 tables?
2) Here is the scenerio: there is a table containing of 4 columns. I need to take the data present in one column and i wrote the proc for the same and i got the data as below
Company
ABC
XYZ
MNL
ILJ
NOM
i need to convert this data ito XML format and send the same from the database to application?
is it possible? if it is possible please let me know?
Thank you in advance
Venu Gopal.K
Software Engineer
INDIA
September 12, 2009 at 7:32 am
You could help us give you a better answer by providing table structure (a CREATE TABLE statement), some sample data as INSERT statements, and the exact output (in XML) that you require. Otherwise, I would have to do all that myself, and guess at the format of the XML you require. I am too lazy for that.
So, here are a couple of links to show you the basics of XML support, including how to insert XML data:
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
http://www.simple-talk.com/sql/t-sql-programming/beginning-sql-server-2005-xml-programming/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 12, 2009 at 11:14 am
Hi Paul,
Here i am providing you all the exact information about my doubt:
1) There are table names like Employee, Role,Details , here i need some data . I achieve the same by using Joins and get the desired O/P what i need. The O/P is as below:
Column1 Column2 Column3 Column4
11 12 13 14
21 22 23 24
31 32 33 34
41 42 43 44
But what my requirement is , I needed the O/P in XML format
2) The second doubt is : I am getting some data in XML format from frontend. Now i need to insert the data in a table by taking and reading the data by column wise (Not in a XML format , but in datatype what the column actually defined was) .
3)Please help me in below queries:
Select from where
Thank you in advance,
Venu Gopal.K
Software Engineer
INDIA
September 12, 2009 at 11:32 am
To get the result in XML format, use a normal select clause and add FOR XML AUTO to it. It will convert the result to one type of XML format.
Since you've decided not to tell us what the result should look like there's little more we can do at this point. One thing would be to point you to BOL to see details of the FOR XML clause. Another option you could try is to search for "XML Workshop" on this site. This will give you a series of articles by J. Sebastian which covers a large variety of XML usage. This should cover question 1 and 2.
Regarding your 3rd question:
Please follow the link in my signature on how to post sample data and don't forget to include your expected result together with what you've tried so far.
September 12, 2009 at 11:56 am
I will provide you the data tomorrow, Give me some time
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 3:46 am
Hi Friends,
The following XML data is getting from frontend (from application). I need to store the data in the table (Not in xml format). How to achieve the below requirement?
The data coming from application is as follows:
VenuGopal
1254874456
Admin
vgsg123
"
I need to store the above data in the table as below:
Table name: Employee
EmpName EmpId EmpRole EmpUserid
VenuGopal.......1254874456 ........Admin.......vgsg123
How can i achieve this?
Please help me in this by providing the exact step by step procedure
Thank you in advance,
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 4:42 am
Thank you.
DECLARE @xml XML;
SET @xml =
N'
VenuGopal
1254874456
Admin
vgsg123
';
DECLARE @Table
TABLE (
emp_id NVARCHAR(10) PRIMARY KEY,
emp_name NVARCHAR(30) NOT NULL,
emp_no INTEGER NOT NULL,
emp_role NVARCHAR(20) NOT NULL
);
INSERT @Table
(
emp_id,
emp_name,
emp_no,
emp_role
)
SELECT Nodes.row.value('./empid[1]', 'nvarchar(10)'),
Nodes.row.value('./empname[1]', 'nvarchar(30)'),
Nodes.row.value('./emp_no[1]', 'integer'),
Nodes.row.value('./emprole[1]', 'nvarchar(20)')
FROM @xml.nodes('row') Nodes (row);
SELECT emp_id,
emp_name,
emp_no,
emp_role
FROM @Table;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:07 am
I am sorry to say this, When i execute the above code which you sent, no rows are getting effected: Showing that the table is empty
I also tried a code, but that's also not getting any rows effected, Could u please review it and let me know where is the error:
alter PROCEDURE InsertUserEmpDetails
@XmlData ntext
AS
BEGIN
--BEGIN TRY
--BEGIN TRANSACTION T1
declare @DocID int
declare @EmpId int
declare @AddRole bit
declare @EditRole bit
declare @DeleteRole bit
declare @SuperAdmin bit
declare @createdby nvarchar(100)
declare @RoleId int
declare @RoleName nvarchar(50)
exec sp_xml_preparedocument @DocID OUTPUT, @XmlData
SELECT * INTO #TempAdminDetails
FROM OPENXML
(@DocID,'/XmlData/AdminDetails',1)
with
(
EmpName nvarchar(50) '@EmpName',
EmpNTId nvarchar(50) '@EmpNTId',
CreatedBy nvarchar(20) '@CreatedBy',
EmpMailId nvarchar(50) '@EmpMailId'
)
EXEC sp_xml_removedocument @DocID
--insert into Admin table
insert into DETAILS select EmpName,EmpNTId,CreatedBy,EmpMailId from #TempAdminDetails
END
Executign the above SP as below:
exec InsertUserEmpDetails
Venu
kshevg
cicmt
110409
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 5:33 am
Make sure you copy *all* the code (notice there is a scroll bar).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:37 am
Yes i did the same , but no luck :crying:
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 5:42 am
Did you copy it straight from the front end or did you use the "Quote" button to see the "real code"?
Reason: SSC displays XML data not exactly as in the source.
In Paul's case, the xml data are all displayed in upper case whereas his original data aren't.
Please note that xml language is case sensitive.
I just tested Pauls solution unsing the quoted source code and it worked just fine.
September 13, 2009 at 5:49 am
Yes ,
Its working ,
Thank you Pauland Lutz
Venu Gopal.K
Software Engineer
INDIA
September 13, 2009 at 5:50 am
Thanks Lutz. Let's try that again, with the xml formatter (see below).
If that fails, I'll attach it as a file!
DECLARE @xml XML;
SET @xml =
N'
VenuGopal
1254874456
Admin
vgsg123
';
DECLARE @Table
TABLE (
emp_id NVARCHAR(10) PRIMARY KEY,
emp_name NVARCHAR(30) NOT NULL,
emp_no INTEGER NOT NULL,
emp_role NVARCHAR(20) NOT NULL
);
INSERT @Table
(
emp_id,
emp_name,
emp_no,
emp_role
)
SELECT Nodes.row.value('./empid[1]', 'nvarchar(10)'),
Nodes.row.value('./empname[1]', 'nvarchar(30)'),
Nodes.row.value('./emp_no[1]', 'integer'),
Nodes.row.value('./emprole[1]', 'nvarchar(20)')
FROM @xml.nodes('row') Nodes (row);
SELECT emp_id,
emp_name,
emp_no,
emp_role
FROM @Table;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:57 am
Paul White (9/13/2009)
Thanks Lutz. Let's try that again, with the xml formatter (see below).If that fails, I'll attach it as a file!
It fails, but it's successful at the same time 🙂
It'll fail if you'd copy it straight from the front end but it will work if you use the "Quote" button and copy the code from there.
It's a known "feature" of SSC when dealing with xml data and it seems like SSC dev's couldn't solve it as per now.
Edit: If you don't see this effect on your system you might run Firefox or Opera. AFAIK this effect is pretty much limited to IE.
September 13, 2009 at 6:07 am
Hey Lutz, so I'm a Chrome 3 person 🙂
File attached. :sigh:
This all seems very hard work :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply