OK - Answer.

Create an XML Schema Collection using:

CREATE XML SCHEMA COLLECTION dbo.MyXMLSchema

AS

N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="rows">

<xs:complexType>

<xs:sequence>

<xs:element name="row" maxOccurs="unbounded" minOccurs="0">

<xs:complexType>

<xs:sequence>

<xs:element type="xs:string" name="col" maxOccurs="unbounded" minOccurs="0"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>'

go

I created the schema definition by using http://www.freeformatter.com/xsd-generator.html and pasting a sample of the XML I needed a schema for.

After creating the schema collection, I changed the code as follows:

DECLARE @string NVARCHAR(MAX)

SET @string=N'<row><col>text</col><col>text</col><col>text</col><col>text</col><col>text</col></row>'

--Build STring for XML

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = N'<rows>' + @string + N'</rows>'

DECLARE @xmlstring XML (dbo.MyXMLSchema)

SET @xmlstring = CONVERT(XML, @string)

DECLARE @table TABLE

(

c1 NVARCHAR(10),

c2 NVARCHAR(10),

c3 NVARCHAR(10),

c4 NVARCHAR(10),

c5 NVARCHAR(10)

)

INSERT INTO @table

SELECT xmls.rndx.value('col[1]', 'nvarchar(10)') AS c1,

xmls.rndx.value('col[2]', 'nvarchar(10)') AS c2,

xmls.rndx.value('col[3]', 'nvarchar(10)') AS c3,

xmls.rndx.value('col[4]', 'nvarchar(10)') AS c4,

xmls.rndx.value('col[5]', 'nvarchar(10)') AS c5

FROM @xmlstring.nodes('rows/row') AS xmls(rndx);

SELECT *

FROM @table

Before Creating XML Schema Collection: 164756 ms

After Creating XML Schema Collection: 360 ms 🙂