Saving a class as XML in SQL

[Mr. Magic]

I have code to convert a class with lots of data in it to XML that I can
then put in SQL. Question is, once it's in SQL, can I process it on the SQL
end? I know on the client side I can take the XML, load it back into the
class and access it there but there are times I'd like to be able to process
it at SQL (in a stored proc).

TIA - Jeff.

 

[Willem van Rumpt]

I have code to convert a class with lots of data in it to XML that I can
then put in SQL. Question is, once it's in SQL, can I process it on the
SQL end? I know on the client side I can take the XML, load it back into
the class and access it there but there are times I'd like to be able to
process it at SQL (in a stored proc).

Assuming you're SQL Server 2005 (I think) or up, then, yes you can.

I have only worked with it on a very small project, so I'm not sure
where or what the limitations are, but AFAIK you can apply all CRUD
operations against a column with datatype "xml".

Other database servers offer similar functionality, but I only have
specific experience with xml with SQL Server (and little at that).

Googling "SQL Server XML querying" or something similar will provide
plenty of results.

 

[Gregory A. Beamer]

I have code to convert a class with lots of data in it to XML that I can
then put in SQL. Question is, once it's in SQL, can I process it on the
SQL end? I know on the client side I can take the XML, load it back into
the class and access it there but there are times I'd like to be able to
process it at SQL (in a stored proc).

Yes. SQL has extensive XML features. You can treat the XML as a table, if
nothing else, and run updates against the "in-memory" table created from the
XML. Google SQL Server and XML and you will see how easy it is to manipulate
the data in XML in SQL Server.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

 

[Alberto Poblacion]

I have code to convert a class with lots of data in it to XML that I can
then put in SQL. Question is, once it's in SQL, can I process it on the SQL
end? I know on the client side I can take the XML, load it back into the
class and access it there but there are times I'd like to be able to
process it at SQL (in a stored proc).

If you are using SQL Server 2005 or above, you can create columns of
type XML and even assign an XML Schema Collection to a column, so that SQL
Server will verify that any data assigned to the column complies with a set
schema. You can then create one or more XML Indexes on that column to speed
up any queries that you perform on the XML.
The XML data can be queried by means of a subset of the XQuery language,
and you can even combine in the same query an XQuery on the XML data with
plain SQL syntax over the non-xml columns of the same table. If you are
doing this in a stored procedure, the XQuery can even refer to the procedure
parameters.

This is an example of the types of things that you can do:

SELECT OneColumn, TheXmlColumn.query(
'declare default element namespace "http://something/myNS";
<MyItems>
{
for $i in /Element1/Element2
return $i
}
</MyItems>') MyItems
FROM TheTable

 

[Arne Vajhøj]

I have code to convert a class with lots of data in it to XML that I can
then put in SQL. Question is, once it's in SQL, can I process it on the
SQL end? I know on the client side I can take the XML, load it back into
the class and access it there but there are times I'd like to be able to
process it at SQL (in a stored proc).

Newer SQLServer has support for XML.

But if you want to do advanced data manipulation, then I would
suggest you use a traditional multi field approach.

Arne