I have some xml files that I want to load into SQL tables. I can use OPENXML and create the table fields and load the document just fine. BUT, is there a way to 1) call the location of the xml document rather than paste all the code and 2) have the table created for me? Below is a SQL example. LMK what you think.
Declare @.idoc int
Declare @.xmldoc varchar (8000)
set @.xmldoc = '
<?xml version="1.0" encoding="utf-8"?>
<ROOT>
<general>
<full_destination_name>London</full_destination_name>
<intro_mini>
London''s contrasts and cacophonies both infuriate and seduce.
</intro_mini>
<intro_short>
London - the grand resonance of its very name suggests history and might. Its opportunities for entertainment by day and night go on and on and on. It''s a city that exhilarates and intimidates, stimulates and irritates in equal measure, a grubby Monopoly board studded with stellar sights.
</intro_short>
<intro_medium>
It''s a cosmopolitan mix of Third and First Worlds, chauffeurs and beggars, the stubbornly traditional and the proudly avant-garde. But somehow - between ''er Majesty and Boy George, Damien Hirst and JMW Turner, Bow Bells and Big Ben - it all hangs together.
</intro_medium>
<intro_quote>
''When a man is tired of London, he is tired of life; for there is in London all that life can afford.'' - Samuel Johnson
</intro_quote>
<timezones>
<timezone>
<gmt_utc>0</gmt_utc>
<timezone_name>Greenwich Mean Time</timezone_name>
</timezone>
</timezones>
<daylight_savings_start>last Sunday in March</daylight_savings_start>
<daylight_savings_end>last Sunday in October</daylight_savings_end>
</general>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmldoc
INSERT INTO General
SELECT * FROM OPENXML(@.idoc, 'ROOT/general',3)
WITH General
EXEC master.dbo.sp_xml_removedocument @.idoc
actually, now I load the xml into MS Visual Studio and create the xsd from there. I then load it into a VB Script and successfully execute a DTS package. Question is, where is the table, data?
Here's the xsd file:
<?xml version="1.0" ?>
<xs
chema id="events" targetNamespace="http://tempuri.org/events.xsd" xmlns:mstns="http://tempuri.org/events.xsd"
xmlns="http://tempuri.org/events.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnchemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="events" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="event">
<xs:complexType>
<xsequence>
<xs:element name="event_name" type="xstring" minOccurs="0" />
<xs:element name="event_from_date" type="xstring" minOccurs="0" />
<xs:element name="event_type" nillable="true" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xsimpleContent msdata:ColumnName="event_type_Text" msdata:Ordinal="1">
<xs:extension base="xstring">
<xs:attribute name="node_id" form="unqualified" type="xstring" />
</xs:extension>
</xsimpleContent>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xschema>
and here's the VB Script:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
Dim objXBulkLoad
Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=SQL-DEV;UID=;PWD=;DATABASE=SQLDBA;"
objXBulkLoad.KeepIdentity = False
'Optional Settings
objXBulkLoad.ErrorLogFile = "c:\temp\NWError.LOG"
objXBulkLoad.TempFilePath = "c:\temp"
'Executing the bulk-load
objXBulkLoad.Execute "C:\TEMP\xml\test_xml\events.xsd", "C:\TEMP\xml\test_xml\events.xml"
Main = DTSTaskExecResult_Success
End Function
shouldn't there now be an events and events and event_type table present?
