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.
Author |
Topic |
romank
Starting Member
2 Posts |
Posted - 2006-02-08 : 08:29:21
|
Hi,Could someone please let me know if there are some good tutorial on parsing xml with tsql. I would like to parse the xml data/values and stored them into other database table.. Thank You,RomanSample XML:<ParameterValues><ParameterValue><Name>TO</Name><Value>myemail@mail.com</Value></ParameterValue><ParameterValue><Name>BCC</Name><Value>myemail@mail.com</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>Customers in Switaly</Value></ParameterValue></ParameterValues> |
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2006-02-11 : 11:31:25
|
Look into the OPENXML command if you are using SQL2000:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.aspIf you are using SQL2005, use the XML datatype with XQUERY. I wrote you a little example to parse the subject (ONLY WORKS IN SQL 2005):declare @myxml xml;select @myxml = '<ParameterValues><ParameterValue><Name>TO</Name><Value>myemail@mail.com</Value></ParameterValue><ParameterValue><Name>BCC</Name><Value>myemail@mail.com</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>Customers in Switaly</Value></ParameterValue></ParameterValues>'--select @myxmlselect @myxml.query('/ParameterValues/ParameterValue/Value[../Name = ''Subject'']') |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-02-11 : 17:59:40
|
Ok, someone please explain why people keep doing this xml stuff.Is it just becasue they like the pain !?rockmoose |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-12 : 15:38:47
|
Seehttp://www.nigelrivett.net/SQLTsql/ParseXML.htmlFor your structureData becomes ParameterValueEntry1 becomes NameEntry2 becomes ValueIt will import the xml file and extract each of the Name, Value pairs. There is a comment where you need to put the processing for the pair.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|