The situation is that I have a .NET application which reads Excel file. These Excel files are converted using DataTable.WriteXML() on the .NET side. The XML is then passed to an XML object in SQL Server 2008. What I need to do now is convert the XML file to a temporary table. The method I have in place requires me to create a temporary table, using the XML object to locate the xs:schema node and find the column list, add the necessary columns to the temporary table, and then locate the data nodes and insert the data one row at a time. This is slow and I was hoping there was an easier method. Below is a small sample XML which I am using.
DECLARE @XML XML = ' <NewDataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="ImportData" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="ImportData"> <xs:complexType> <xs:sequence> <xs:element name="DOMICILE" type="xs:string" minOccurs="0" /> <xs:element name="LP" type="xs:string" minOccurs="0" /> <xs:element name="PUROUTE" type="xs:string" minOccurs="0" /> <xs:element name="ROUTE" type="xs:string" minOccurs="0" /> <xs:element name="RUN" type="xs:string" minOccurs="0" /> <xs:element name="RTMILES" type="xs:string" minOccurs="0" /> <xs:element name="LEGMILES" type="xs:string" minOccurs="0" /> <xs:element name="RTSTOPS" type="xs:string" minOccurs="0" /> <xs:element name="SEQUENCE" type="xs:string" minOccurs="0" /> <xs:element name="SUPPLIER" type="xs:string" minOccurs="0" /> <xs:element name="NAME" type="xs:string" minOccurs="0" /> <xs:element name="LDK" type="xs:string" minOccurs="0" /> <xs:element name="PDK" type="xs:string" minOccurs="0" /> <xs:element name="PLANT" type="xs:string" minOccurs="0" /> <xs:element name="LOGPT" type="xs:string" minOccurs="0" /> <xs:element name="ARRVDAY" type="xs:string" minOccurs="0" /> <xs:element name="ARRVTIME" type="xs:string" minOccurs="0" /> <xs:element name="DEPDAY" type="xs:string" minOccurs="0" /> <xs:element name="DEPTIME" type="xs:string" minOccurs="0" /> <xs:element name="UNLDDAY" type="xs:string" minOccurs="0" /> <xs:element name="UNLDSTRT" type="xs:string" minOccurs="0" /> <xs:element name="UNLDFNSH" type="xs:string" minOccurs="0" /> <xs:element name="RELDDAY" type="xs:string" minOccurs="0" /> <xs:element name="RELDSTRT" type="xs:string" minOccurs="0" /> <xs:element name="RELDFNSH" type="xs:string" minOccurs="0" /> <xs:element name="S" type="xs:string" minOccurs="0" /> <xs:element name="M" type="xs:string" minOccurs="0" /> <xs:element name="T" type="xs:string" minOccurs="0" /> <xs:element name="W" type="xs:string" minOccurs="0" /> <xs:element name="R" type="xs:string" minOccurs="0" /> <xs:element name="F" type="xs:string" minOccurs="0" /> <xs:element name="A" type="xs:string" minOccurs="0" /> <xs:element name="DAYSRUN" type="xs:string" minOccurs="0" /> <xs:element name="PUFREQ" type="xs:string" minOccurs="0" /> <xs:element name="ORDERS" type="xs:string" minOccurs="0" /> <xs:element name="ORDERTOTAL" type="xs:string" minOccurs="0" /> <xs:element name="WKDAY" type="xs:string" minOccurs="0" /> <xs:element name="M3" type="xs:string" minOccurs="0" /> <xs:element name="KG" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <ImportData> <DOMICILE>8888P</DOMICILE> <PUROUTE>HPD-01</PUROUTE> <ROUTE>HPD</ROUTE> <RUN>1</RUN> <RTMILES>540</RTMILES> <LEGMILES>0</LEGMILES> <RTSTOPS>1</RTSTOPS> <SEQUENCE>1</SEQUENCE> <SUPPLIER>8888P</SUPPLIER> <ARRVDAY>1</ARRVDAY> <ARRVTIME>05:05</ARRVTIME> <DEPDAY>1</DEPDAY> <DEPTIME>05:20</DEPTIME> <S>N</S> <M>Y</M> <T>Y</T> <W>Y</W> <R>Y</R> <F>Y</F> <A>N</A> <DAYSRUN>5</DAYSRUN> </ImportData> </NewDataSet> '