XML upload to SQL server 2000

[beaniebear]

I have been trying to upload XML to a SQL server database. Using an XDR schema and a vbs file utilising the SQLXMLBulkLoad object.

This works fine until I get to the <story></story> tags which have other elements such as <i></i> and <p></p> within.

Example XML :-
<allnews>
<news>
<headline>News Article</headline>
<shortheadline>News</shortheadline>
<source>A newspaper</source>
<story>Example of a <i>Story</i></story>
</news>
</allnews>

Example XDR :-

<Schema xmlns=&quot;urn:schemas-microsoft-com:xml-data&quot;
xmlns:dt=&quot;urn:schemas-microsoft-com:xml:datatypes&quot;
xmlns:sql=&quot;urn:schemas-microsoft-com:xml-sql&quot; >

<ElementType name=&quot;headline&quot; dt:type=&quot;string&quot; />
<ElementType name=&quot;shortheadline&quot; dt:type=&quot;string&quot; />
<ElementType name=&quot;source&quot; dt:type=&quot;string&quot; />
<ElementType name=&quot;story&quot; dt:type=&quot;string&quot; />

<ElementType name=&quot;allnews&quot; sql:is-constant=&quot;1&quot;>
<element type=&quot;news&quot; />
</ElementType>

<ElementType name=&quot;news&quot; sql:relation=&quot;News&quot;>
<element type=&quot;headline&quot; sql:field=&quot;Headline&quot; />
<element type=&quot;shortheadline&quot; sql:field=&quot;ShortHeadline&quot; />
<element type=&quot;source&quot; sql:field=&quot;source&quot; />
<element type=&quot;story&quot; sql:field=&quot;story&quot; />
</ElementType>

</Schema>

Example VBS :-

set objBL=createobject(&quot;SQLXMLBulkLoad.SQLXMLBulkLoad&quot
objBL.ConnectionString = &quot;&quot;
objBL.ErrorLogFile = &quot;c:\error.log&quot;
objBL.Execute &quot;C:\newsmapping.xml&quot;, &quot;c:\news.xml&quot;
set objBL = nothing

How can I make this ignore any styling tags such as bold, italics etc?

Also is this the best way to get data into a SQL database? Would another way be simlper?

Thanks, Jo

 

[whichman]

try using CDATA or add <i> and <p> to your schema Build web applications faster with a few lines of XML Code using DataML[tm]

http://dataml.net

 

[beaniebear]

Not sure that I can add the formatting to the schema as every record will contain differing numbers of <i> and <p> tags. Also I want to store the story in one field in the database. ???

Can you explain what you mean by use CDATA?

Thanks, Jo