?
Solved

How to store html in SQL database and output to xml

Posted on 2011-05-11
7
Medium Priority
?
223 Views
Last Modified: 2012-05-11
Hi

I need to create an XML for a third party  so we can share some XML articles.
I want  to store each node in a data base field using SQL Server 2008
I will input the html with an html editor through a webform (asp.net)
I need the output to be well formed xml where special characters are properly escaped.
Do I need to encode the html when we save it?
What would be the best XML encoding to use on the feed?
We don't have a lot of experience of this so advice would be really helpful.
0
Comment
Question by:nhmedia
  • 2
  • 2
  • 2
6 Comments
 
LVL 8

Accepted Solution

by:
cubaman_24 earned 2000 total points
ID: 35738272
Hello:
Tidy can rescue you ;-)
http://sourceforge.net/projects/tidynet/
If you are not using special characters, UTF-8 will be fine.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35747992
I suggest you choose the encoding based on your input chars and here's more detail about it:

http://www.w3schools.com/XML/xml_encoding.asp

As far as storing it and reading it from SQL I suggest you do it by using sql code like in the example below:


CREATE PROCEDURE dbo.my_XML_update
      @XMLData NTEXT,
      @myId SMALLINT
AS
SET NOCOUNT ON

-- Retrieve XML data
DECLARE @hocDoc INT

DECLARE @Comments NVARCHAR(2048)

EXEC SP_XML_PREPAREDOCUMENT @hocDoc OUTPUT, @XMLData

-- OPEN XML
-- Param1 is Pointer to DOC
-- Param2 is XML doc hiearchy
-- Param3 is Use Element Structure rather than Attributes


SELECT
      ContactId,
      ContactName
INTO
      #tmpContact
FROM
      OPENXML(@hocDoc, '/root/Contact', 2)
WITH (ContactId  INTEGER, ContactName NVARCHAR(50))

--this is how you populate the contact xml
SELECT Contact.ContactId,
            ISNULL(Contact.ContactName, '') AS ContactName
FROM #tmpContact Contact, Contact C
WHERE      Contact.ContactId = C.Id
ORDER BY      Contact.ContactName
FOR XML AUTO, ELEMENTS

-- Then you can do an INSERT
INSERT INTO Contact (Id, ContactName)
      SELECT ContactId,ContactName
      FROM #tmpContact

--or an UPDATE
UPDATE Contact SET ContactName = t.ContactName
FROM #tmpContact t
                  WHERE      contact.Id = #tmpContact.ContactId
            
-- Drop the temp Table
DROP TABLE #tmpContact

-- and MUST release XML doc to avoid memeory issues
EXEC SP_XML_REMOVEDOCUMENT @hocDoc
GO

0
 

Author Comment

by:nhmedia
ID: 35772977
Thanks, but I am not trying to store xml. I want to store text in varchar(max) fields, then output it in xml format. The text will contain html and other problem characters. These need to be escaped. I want to know if this is done when the html is outputted (I guess through the choice of encoding). Or when I save the data in my web page. Sorry if that is not clear...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 8

Expert Comment

by:cubaman_24
ID: 35775634
Hello:
Is I said before, Tidy is capable of escaping and format html to produce well formed xml. Give it a try.
http://sourceforge.net/projects/tidynet/
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35777606
You said "I want  to store each node in a data base field using SQL Server 2008" - right?
I believe my suggestion above it gives you just that by using SQL code where data is stored in a SQL table column (text, or varchar) like ContactName for instance. Good luck!
0
 

Author Closing Comment

by:nhmedia
ID: 35805114
I found this worked
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question