Convert XML into Temporary Table w/ Data

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>
'

August 2nd, 2013 4:22pm

you can directly convert excel to temp tables
Free Windows Admin Tool Kit Click here and download it now
August 3rd, 2013 5:19am

Refer the below technique,

-- xml PARAMETER AND INSERT INTO TABLE 
-- OR CREATE TEMP TABLE USING SELECT INTO
--------------------------------------
declare @handle int
declare @XML xml 
set @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>'
EXEC sp_xml_preparedocument @handle OUT, @XML
----------------
SELECT * INTO TEMPTAB FROM OPENXML(@handle, '/NewDataSet/ImportData',2) 
WITH (DOMICILE nvarchar(100),PUROUTE nvarchar(100),ROUTE nvarchar(100),
	RUN nvarchar(100),RTMILES nvarchar(100),LEGMILES nvarchar(100),
	RTSTOPS nvarchar(100),SEQUENCE nvarchar(100),SUPPLIER nvarchar(100)
)
---------------
EXEC sp_xml_removedocument @handle

Regards, RSingh


August 3rd, 2013 6:08am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics