Use SQLXML to import XML

Question

Use SQLXML to import XML

Nikki-686957

Valued Member

Points: 68
More actions
March 25, 2008 at 9:52 pm

#76935

I've a xml file like below
Table is
CREATE TABLE [dbo].[Cust] (
[CustomerID] [int] NOT NULL ,
[CompanyName] [varchar] (20) NOT NULL ,
)
Because the data is huge, I want to use SQLXML to preform bulk load. How to write the schema for this xml, is below right? But I always get '' relationship expected on 'CustomerID' "
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
Thanks a lot

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

Can you edit the sample XML to remove the angled brackets? I usually use square brackets and comment the code. The angled brackets results in blank data as you see above.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Alternatively - simply do a "find/replace", replacing your angled brackets with their HTML equivalent.

That's

replace < with &lt;

replace > with &gt;

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nikki-686957 Valued Member Points: 68 More actions · Answer 3

Oh, sorry.

The xml data file is:

<?xml version="1.0" ?>

<root>

</Customers>

</root>

The schema file is:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="root" sql:is-constant="1">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Customers" sql:relation="Cust" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="CustomerID" >

<xsd:complexType>

<xsd:attribute name="value" type="xsd:integer" sql:field="CustomerID" />

</xsd:complexType>

</xsd:element>

<xsd:element name="CompanyName" >

<xsd:complexType>

<xsd:attribute name="value" type="xsd:string" sql:field="CompanyName" />

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>