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 http://www.xml.com/pub/a/2000/11/29/schemas/part1.html 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"><?xml version="1.0" standalone="yes"?><br /><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="http://www.w3.org/2001/XMLSchema" 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"><br /> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"><br /> <xs:complexType><br /> <xs:choice minOccurs="0" maxOccurs="unbounded"><br /> <xs:element name="RnCategory"><br /> <xs:complexType><br /> <xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />equence><br /> <xs:element name="Code" type="xs:int" minOccurs="0" /><br /> <xs:element name="Description" type="xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tring" minOccurs="0" /><br /> </xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />equence><br /> </xs:complexType><br /> </xs:element><br /> </xs:choice><br /> </xs:complexType><br /> </xs:element><br /></xs<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chema></font id="code"></pre id="code"><br />and this xml file<pre id="code"><font face="courier" size="2" id="code"><?xml version="1.0" standalone="yes"?><br /><NewDataSet><br /> <List><br /> <Code>1</Code><br /> <Description>ABC</Description><br /> </List><br /> <List><br /> <Code>2</Code><br /> <Description>XYZ</Description><br /> </List> <br /></NewDataSet></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.
]]>