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 2000 Forums
 Transact-SQL (2000)
 TSQL to parse xml

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,

Roman

Sample 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.asp


If 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 @myxml

select @myxml.query('/ParameterValues/ParameterValue/Value[../Name = ''Subject'']')


Go to Top of Page

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
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-12 : 15:38:47
See
http://www.nigelrivett.net/SQLTsql/ParseXML.html

For your structure
Data becomes ParameterValue
Entry1 becomes Name
Entry2 becomes Value

It 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.
Go to Top of Page
   

- Advertisement -