Storing HTML into a XML-field
-
Hey guys,
I'm looking for a way to write a HTML-Snippet into a XML-field. Can you help me on that one? I don't need to write actual HTML-Tags but only HTML-entities.
My first idea was to JUST WRITE IT into an attribute as CDATA. But sadly MSSQL does some checking and I always get an error: 'XML parsing: line 1, character 119, well formed check: undeclared entity'. Is there an easier way than to define all entities?!
I'm stuck on this for quite a while now, and I don't see a solution... Can I bypass the checking somehow? Can I simply include the entities from w3c somehow?
Yours
-
It sounds like you want to store some text that is not valid xml in an xml field? Can you use a nvarchar field instead?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Actually I'm using an varchar-field (because of the privacy policy). The error occurs when I cast it into xml. But I need to be able to cast it, because I need to use it in the WHERE-clause and some views.
It's an HTML-String. Of course that's not well-formed standalone XML (the entities like
"and>are not defined). Because the field is limited to 1500 characters, I cannot include the definitions every time. Can I somehow include them from a file (or better: w3c directly)? That would solve all my problems. -
I can't help here but I have to ask... why would anyone store either XML or HTML in a database?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If it takes one and a half year to get the approvement of your data privacy policy and the actual database. If you want to store temporary additional information with extra privacy policy you don't want to go through all prior steps to change the database
but rather write the information in the commentary field in a way you can automized read. But what to do with the 'bad' characters in the commentory like "... replacing them with their HTML-entity " and write them into a XML-snippet into the commentary field is the easiest and cleanest solution I can think of. -
After reading your latest posts I am more confused about what you are trying to do than I was before. I don't understand why you have to convert html to xml for comparison. Maybe if you posted the ddl and some sample data and what you are trying to do we can help.
Jeff - I have lots of places where I store html in the db. I have a number of places that allow admin users to use an html editor to define the contents of pages and such. The easiest way is to store this in the db and stream it on request. Keeps me from letting IIS have create privileges on the web server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
In addition to what Sean said, a lot of out-of-the-box portal software, like Dot Net Nuke and Sharepoint store the html in fields in the database, for dynamic content; codebase on the web server is static, never changes, just the data served is dynamic.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Oh yeah :-). Excuse me. I always try to keep the description as generic as possible so that other people can profit from the solution as well. But that leads to confusion ;-). Ok. I simply use several HTML-snippet in an XML-string and want to cast that string to an XML-object. Here is what I do in every detail:
HTML-comments I want to store (eventually containing HTML-entities) -> several PHP-strings -> one XML-object in PHP -> one XML-string in PHP -> Query-string (escaped special chars for SQL) -> Database table field (varchar)
Here is what doesn't work:
Database table field (varchar) -> XML-object (via cast)
Here is the error message:
'XML parsing: line 1, character 119, well formed check: undeclared entity'
OK that's right: I do not want to define every HTML-Entity in a standalone XML snippet
Example:
UPDATE [Table] SET [YearlyComments] = '<Root><Comment year="2009" info="This will & quot;kill& quot; my server..." /><AdditionalData bit="1" int="12345" /></Root>'GOPlease remove the spacebar between & and quot; in your mind :-(. I wasn't able to write the intended character combination in this forum.
SELECT CAST([Table].[YearlyComments] AS xml).value('(/Root/AdditionalData/@bit)[1]', 'bit') AS [PseudoFieldName]GO=> ERROR!
SELECT * FROM [Table] WHERE CAST([YearlyComments] AS xml).value('(/Root/AdditionalData/@bit)[1]', 'varchar(1)') = '1'GO=> ERROR!
And so on...
Here is why I use this 'complicated' method:
- I don't want to change the database structure
- I want to offer the possibility to leave a comment every year (that would otherwise only work with two extra tables; see above)
- I also want to use this information in WHERE-clauses
A solution might be:
- Having a DTD-file (on my server or on w3c's) defining all HTML-entities http://www.w3.org/TR/html40/sgml/entities.html
- Bypassing the well-formed check
- Something I didn't think of
Not a solution would be:
- Including the Entity-Definitions in every table field (it's length is 1500 because a longer varchar can not be passed through the ODBC driver)
Yours
alphanoob
-
Square peg meet my friend round hole.
The string you have
'<Root><Comment year="2009" info="This will " kill " my server..." /><AdditionalData bit="1" int="12345" /></Root>'is not valid xml hence the reason it bombs when trying to cast it as xml.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Tell me something new!
It would be valid stand-alone xml if I would be able to include these damn HTML-entity definitions... -
The problem is you have mixed quotes.
info="This will " kill " my server..."change it to
info="This will '' kill '' my server..."and it will be fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
SELECT CAST('<Root><Comment year="2009" info="This will ''kill'' my server..." /><AdditionalData bit="1" int="12345" /></Root>' AS xml).value('(/Root/AdditionalData/@bit)[1]', 'bit') AS [PseudoFieldName]This works just fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply