xml question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

xml question

Author

Topic

ter1234
Starting Member

2 Posts

Posted - 2010-07-29 : 07:16:48

I am new to sql so sorry for the question
i have a stored procedure that receives XML:
@students XML
that is like this
<Students>
<Student><Name>O1</Name><ID>1</ID></Student>
<Student><Name>O2</Name><ID>2</ID></Student>
</Students>

i have a table of students
with ID column and name column
how do i insert the xml into the table the nodes of studenst?
sample code will be very helpfull...

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-29 : 07:19:28

I have a blog article to maybe assist you with your question:

http://www.sql-programmers.com/Blog/tabid/153/EntryId/10/XML-Data-Type-in-SQL-Server-2005.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

ter1234
Starting Member

2 Posts

Posted - 2010-07-29 : 07:25:39

Thanks... can ypu pls show me how to do that i just dont understan this subject

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-29 : 07:28:09

CREATE TABLE #tblXML (ID INT PRIMARY KEY, xmlVal XML not null)
declare @x as varchar(max)
set @x='<Students>
<Student><Name>O1</Name><ID>1</ID></Student>
<Student><Name>O2</Name><ID>2</ID></Student>
</Students>'

INSERT #tblXML VALUES(2, @x)

SELECT x.query('ID').value('.','INT')
as ID,x.query('Name').value('.','varchar(100)') as Product
FROM #tblXML CROSS APPLY xmlVal.nodes('/Students/Student') AS Tref(x)

drop table #tblXML

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources