More efficient WITH XMLNAMESPACES insert?

I am a newbie to XML and am trying to develop a SQL query in SSMS 2008 to insert new XML records. I have a really long XML file that I'm trying to populate into a new datatable called Clas_Control_Set.

My current solution works but is not ideal because this file is so large that I have to run this in stages since it is too large to copy and paste. Is there any way I can do this all in one step? Here is my table structure:

CREATE TABLE Clas_Control_Set(
CCS_DSP_TRM_LIT VARCHAR(10),
CONTROL_SET_ID VARCHAR(10),
ENTITY_ID INT,
SCHOOL_YEAR INT,
CCS_DSP_STR_TRM INT,
CCS_DSP_STP_TRM INT,
--TRACK INT,
COR_LENGTH_SET_ID VARCHAR(10))

And a tiny amount of data I'm trying to insert:

DECLARE @xmlDocument XML

SET @xmlDocument = '
<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="c0" rs:name="CCS-DSP-TRM-LIT" rs:number="1" rs:nullable="true">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="6" />
      </s:AttributeType>
      <s:AttributeType name="c1" rs:name="CONTROL-SET-ID" rs:number="2">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="4" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c2" rs:name="ENTITY-ID" rs:number="3">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c3" rs:name="SCHOOL-YEAR" rs:number="4">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c4" rs:name="CCS-DSP-STR-TRM" rs:number="5" rs:nullable="true">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" />
      </s:AttributeType>
      <s:AttributeType name="c5" rs:name="CCS-DSP-STP-TRM" rs:number="6" rs:nullable="true">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" />
      </s:AttributeType>
      <s:AttributeType name="TRACK" rs:number="7" rs:write="true">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c7" rs:name="COR-LENGTH-SET-ID" rs:number="8">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="6" rs:maybenull="false" />
      </s:AttributeType>
      <s:extends type="rs:rowbase" />
    </s:ElementType>
  </s:Schema>
   <rs:data>
    <z:row c0="Q4S" c1="S2" c2="095" c3="634" c4="4" c5="4" TRACK="0" c7="SM" />
    <z:row c0="Q4S" c1="S2" c2="095" c3="635" c4="4" c5="4" TRACK="0" c7="SM" />
    <z:row c0="Q4S" c1="S2" c2="095" c3="636" c4="4" c5="4" TRACK="0" c7="SM" />
    <z:row c0="Q4S" c1="S2" c2="095" c3="637" c4="4" c5="4" TRACK="0" c7="SM" />
    <z:row c0="Q4S" c1="S2" c2="095" c3="638" c4="4" c5="4" TRACK="0" c7="SM" />
  </rs:data>
</xml>
'

;WITH XMLNAMESPACES( 'urn:schemas-microsoft-com:rowset' AS rs, '#RowsetSchema' AS z )
INSERT INTO dbo.Clas_Control_Set ( CCS_DSP_TRM_LIT, CONTROL_SET_ID, ENTITY_ID, SCHOOL_YEAR, CCS_DSP_STR_TRM,
CCS_DSP_STP_TRM, COR_LENGTH_SET_ID )

SELECT
    x.y.value('@c0', 'VARCHAR(10)'),
    x.y.value('@c1', 'VARCHAR(10)'),
    x.y.value('@c2', 'INT'),
    x.y.value('@c3', 'INT'),
    x.y.value('@c4', 'INT'),
    x.y.value('@c5', 'INT'),
    x.y.value('@c7', 'VARCHAR(10)')
FROM @xmlDocument.nodes('xml/rs:data/z:row') x(y)

The data above I get from the following query:

CREATE TABLE XmlImportTest2(
xmlFileName VARCHAR(MAX) NOT NULL,
xml_data XML NOT NULL
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\Users\Administrator\Documents\data\Clas_Control_Set.xml'

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO XmlImportTest2(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

--drop table XmlImportTest2

SELECT * FROM XmlImportTest2

In order for me to even see the records from this second query I had to increase SQL character count since it returns more than 15000 lines. Is there a way to incorporate this second query into the first one?

ansaurus

tags:

views:

answers:

More efficient WITH XMLNAMESPACES insert?

related questions