Import XML into SQl Server 2005
I am trying to design a SQL database. I ahve created a table, dbo.PFS in the PFS database. I have an XML file I would like to import into it. I have created a table in the exact structure listed in the XML.
The code attached is the schema imbedded in my XML file. My table structure makes exactly. How do I import this XML into my table? I havce tried OpenRowSourse, but I run this query:
INSERT INTO dbo.pfs(schoolyear, code, rcdtcode, name, address1, address2,
city, state, zip, countycode, phone, fax, contact, contacttitle, email, ceo,
progtypecode, gendercode, approved, appreviewername, active, inactive,
regularbegin, regularend, regulardays, summerbegin, summerend, summerdays,
approvedregularbegin, approvedregularend, approvedsummerbegin, approvedsummerend,
approvedsummerdays, authorizingagentname, signature, comment, publish)
select * from openrowset(
bulk 'j:\privfac\2011privfac.xm
I get the error:
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
What am I doing wrong? I have never used SQL server before, I have been a VFP programmer for years.
The code attached is the schema imbedded in my XML file. My table structure makes exactly. How do I import this XML into my table? I havce tried OpenRowSourse, but I run this query:
INSERT INTO dbo.pfs(schoolyear, code, rcdtcode, name, address1, address2,
city, state, zip, countycode, phone, fax, contact, contacttitle, email, ceo,
progtypecode, gendercode, approved, appreviewername, active, inactive,
regularbegin, regularend, regulardays, summerbegin, summerend, summerdays,
approvedregularbegin, approvedregularend, approvedsummerbegin, approvedsummerend,
approvedsummerdays, authorizingagentname, signature, comment, publish)
select * from openrowset(
bulk 'j:\privfac\2011privfac.xm
I get the error:
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
What am I doing wrong? I have never used SQL server before, I have been a VFP programmer for years.
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='intSchYr' rs:number='1' rs:writeunknown='true'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrFacCd' rs:number='2' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='3' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrRCDTS' rs:number='3'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='15' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrFacName' rs:number='4' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrAddr1' rs:number='5' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrAddr2' rs:number='6' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrCity' rs:number='7' rs:nullable='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='8000'/>
</s:AttributeType>
<s:AttributeType name='vchrState' rs:number='8' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='2' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrZip' rs:number='9' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='5' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrCountyCd' rs:number='10' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='3' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrPhone' rs:number='11' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrFax' rs:number='12' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrContact' rs:number='13' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrContactTitle' rs:number='14' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrEmail' rs:number='15' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='100' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrCEO' rs:number='16' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrOpAgencyCd' rs:number='17' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='3' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrProgTypeCd' rs:number='18' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='1' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrGenderCd' rs:number='19' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='1' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmApproved' rs:number='20'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrAppRevName' rs:number='21' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmActive' rs:number='22'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmInactive' rs:number='23'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmCalRegBegin' rs:number='24'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmCalRegEnd' rs:number='25'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='intCalRegDays' rs:number='26' rs:writeunknown='true'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmCalSummerBegin' rs:number='27'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmCalSummerEnd' rs:number='28'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='intCalSummerDays' rs:number='29' rs:writeunknown='true'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmAprvdRegBegin' rs:number='30'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmAprvdRegEnd' rs:number='31'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='intAprvRegDays' rs:number='32' rs:writeunknown='true'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmAprvdSummerBegin' rs:number='33'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmAprvdSummerEnd' rs:number='34'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='intAprvSummerDays' rs:number='35' rs:writeunknown='true'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrAuthAgentName' rs:number='36' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='33' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='dtmSignature' rs:number='37'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='10' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrComment' rs:number='38' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='100' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='vchrPublishSw' rs:number='39' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='1' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The code above is an example to help you understand how to import XML file in SQL table directly from SQL stored procedure
ASKER