In SQL Server 2005 we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.
There are few methods defined in SQL Server 2005 for XML DataType like:
* nodes
* query
* value
Sample Code:
DECLARE @XmlData xml
SET @XmlData='<Library>
<Subject name="ASP.NET">
<Book ID="1">
<Author>Lakhan Pal Garg</Author>
<Title>ASP.NET Tips</Title>
<Price>$100</Price>
</Book>
<Book ID="2">
<Author>Lakhan Pal Garg</Author>
<Title>SQL Server Tips</Title>
<Price>$90</Price>
</Book>
</Subject>
<Subject name="XML">
<Book ID="3">
<Author>Peter</Author>
<Title>XSLT Tutorial</Title>
<Price>$140</Price>
</Book>
<Book ID="4">
<Author>Rihana</Author>
<Title>XML Parsing in SQL Server</Title>
<Price>$120</Price>
</Book>
</Subject>
</Library>'
select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)
In the above Select Statement we have used @XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|