Inserting XML | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Inserting XML

Can someone give me an example of an xml mapping schema? I found an article talking about loading an xml into sql using bulk insert and then I followed this to create a schema but it won’t save into my table. Can someone please guide me. Thanks!
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = "provider=SQLOLEDB;data source=DS;database=Northwind;Integrated Security=SSPI;"
objBulkLoad.Execute "c:xmlOrdersSchema.xml", "c:xmlOrdersData.xml"
Set objBulkLoad = Nothing

I have this xml schema <pre id="code"><font face="courier" size="2" id="code">&lt;?xml version="1.0" standalone="yes"?&gt;<br />&lt;xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chema id="NewDataSet" xmlns="" xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />s="" xmlns:msdata="urn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chemas-microsoft-com<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />ml-msdata"&gt;<br /> &lt;xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"&gt;<br /> &lt;xs:complexType&gt;<br /> &lt;xs:choice minOccurs="0" maxOccurs="unbounded"&gt;<br /> &lt;xs:element name="RnCategory"&gt;<br /> &lt;xs:complexType&gt;<br /> &lt;xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />equence&gt;<br /> &lt;xs:element name="Code" type="xs:int" minOccurs="0" /&gt;<br /> &lt;xs:element name="Description" type="xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tring" minOccurs="0" /&gt;<br /> &lt;/xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />equence&gt;<br /> &lt;/xs:complexType&gt;<br /> &lt;/xs:element&gt;<br /> &lt;/xs:choice&gt;<br /> &lt;/xs:complexType&gt;<br /> &lt;/xs:element&gt;<br />&lt;/xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chema&gt;</font id="code"></pre id="code"><br />and this xml file<pre id="code"><font face="courier" size="2" id="code">&lt;?xml version="1.0" standalone="yes"?&gt;<br />&lt;NewDataSet&gt;<br /> &lt;List&gt;<br /> &lt;Code&gt;1&lt;/Code&gt;<br /> &lt;Description&gt;ABC&lt;/Description&gt;<br /> &lt;/List&gt;<br /> &lt;List&gt;<br /> &lt;Code&gt;2&lt;/Code&gt;<br /> &lt;Description&gt;XYZ&lt;/Description&gt;<br /> &lt;/List&gt; <br />&lt;/NewDataSet&gt;</font id="code"></pre id="code">How can import this to my table?
Sorry guys, just found out that I need to map elements into sql objects. I just need to find a way how I can do it using dataset in .net. Thanks.
All I know is that there is barely any support for XML in SQL 2000. My experiences have been with exporting to XML from a job … in the end, we had to fake all the tags in select statements, and use a heck of a lot of UNION ALLs. And don’t even mention unicode characters … We got there in the end, but every turn of the way there were surprises.
Well, good things there is SQLXML 3.0 Bulk Load component. Also I believe we can use DTS ActiveX Script & SQLXML 3.0 Bulk Load. I hope this is what you mean[?] Still that’s all I know that it can be use but can’t make it to work [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]
Sorry, I was just venting recent frustrations outputting data in XML format. You know, the kind of thing when you expect to say: here’s my query, here’s my list of corresponding tags, and here’s the magic FOR XML parameter that does it exactly the way I want it – which is what it claims it does, but it doesn’t.