Data Transformations, or something like that, with SQL

Right, so I'm currently trudging through the SQL video tutorials and such, so it may be that I get to this sooner or later, but as I'm under a deadline, I thought I'd post this question beforehand so I can use that info with what I'm learning now.

Here's my situation: I have a ASP.NET 2.0 site in which I currently use XML files to display the text on the page, and I transform that text using an XSL stylesheet. I want to move that data to a database, but I'm not sure what is the best way to do that. Basically what I'm most concerned with is storing the main text (paragraphs with embedded hyperlinks). Currently, I can get the XSL to pick out the links and transform them from simply XML data to live links when they display on the page, but would I be able to do the same if I were pulling these paragraphs out of a database? Or should I just store the XML data in the database, and still pull that out so I can transform it appropriately with the XSL sheet I already have? (For that matter, can I dynamically write XML content to a database? Or am I just better off keeping my XML files?) What's the best approach for something like this?

Thanks for the help!

Hi,

I store my XML in the database with the field type "XML" and transform the XML dynamicly. Below is some code which queries the database and displays the XML as HTML:

 Try Dim myconnection As Data.SqlClient.SqlConnection myconnection = New Data.SqlClient.SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString Dim strSQL As String ="SELECT xml FROM myxml WHERE id = 21"

 Dim dbComm As New Data.SqlClient.SqlCommand(strSQL, myconnection) myconnection.Open() Dim myXR = dbComm.ExecuteXmlReader() Dim sb As New StringBuilder() While myXR.read() sb.Append(myXR.readouterxml()) End While Dim xml As String = sb.ToString() Dim finxml As String = sb.ToString() Dim xmldoc As New XmlDocument xmldoc.LoadXml(xml) Dim stWrite As StringWriter = New StringWriter() Dim xslt As New XslCompiledTransform() xslt.Load(Server.MapPath("myxslt.xslt")) xslt.Transform(xmldoc, Nothing, stWrite) Response.Write(stWrite.ToString()) Me.TextBox1.Text = xml.ToString() dbComm.Dispose() Catch ex As Exception Response.Write(Err.Description) End Try

Also, I wrote a tutorial on how to load (whole directories) XML files in a SQL database:

http://www.aviationcontentmanagement.com/Article.htm

|||

That looks really helpful, but I'm coding in C#, and I'm having a bit of trouble translating this...can anyone help? Thanks!!

|||

Better yet, can I modify the code to do the following?

So I have a paragraph of text that appears like this which is drawn from a SQL field formatted for XML:

This is a test paragraph of text that appears as is directly from the database, complete with <link href="target.html">xml tags</link> that I'll format using XSL style sheet.

...can I write that output, from the SQL, to a XmlDocument, then set that as the source document of my XML control on the page? Specifically, how can I take the data from SQL and dump it into a XmlDocument object? Thanks for the help!!

|||

Try this. i am not a C# guy, but it should work:

Data.SqlClient.SqlConnection myconnection;myconnection =new Data.SqlClient.SqlConnection();myconnection.ConnectionString = ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString;string strSQL ="SELECT xml FROM myxml WHERE id = 21";Data.SqlClient.SqlCommand dbComm =new Data.SqlClient.SqlCommand(strSQL, myconnection);myconnection.Open();object myXR = dbComm.ExecuteXmlReader();StringBuilder sb =new StringBuilder();while (myXR.read()) {sb.Append(myXR.readouterxml());}string xml = sb.ToString();string finxml = sb.ToString();XmlDocument xmldoc =new XmlDocument();xmldoc.LoadXml(xml);StringWriter stWrite =new StringWriter();XslCompiledTransform xslt =new XslCompiledTransform();xslt.Load(Server.MapPath("myxslt.xslt"));xslt.Transform(xmldoc,null, stWrite);Response.Write(stWrite.ToString());this.TextBox1.Text = xml.ToString();dbComm.Dispose();