|
Home > Archive > MS SQL XML > October 2006 > SQLXML3 schema
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi
I am new using XML and using SQLXML3 to import XML files into SQL Server
2000 so was looking for a suitable schema which could import sample xml
listed into this table
CREATE TABLE Employees
(
EmployeeID CHAR(4) PRIMARY KEY,
Employee VARCHAR(50),
Position VARCHAR(50)
)
<ROOT>
<Employees>
<Employee ID="1212">Joe Smith</Employee>
<Position>Sales Manager</Position>
<Employee ID="1213">Bekky Carter</Employee>
<Position>Sales Assistant</Position>
</Employees>
</ROOT>
| |
| Monica Frintu [MSFT] 2006-10-28, 7:30 pm |
| Hello,
Here is a sample schema:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="ROOT" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="Employees" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="Employee" type="EmployeeType"
sql:relation="Employees" sql:field="Employee"/>
<xs:element name="Position" sql:relation="Employees"
sql:field="Position"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="EmployeeType">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="ID" sql:relation="Employees" sql:field="EmployeeID"/>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:schema>
But you'll end up with the following in your database:
Joe Smith 1212 NULL
NULL NULL Sales Manager
Bekky Carter 1213 NULL
NULL NULL Sales Assistant
Hope this helps.
Regards,
--
Monica Frintu
"AndyH" wrote:
> Hi
>
> I am new using XML and using SQLXML3 to import XML files into SQL Server
> 2000 so was looking for a suitable schema which could import sample xml
> listed into this table
>
> CREATE TABLE Employees
> (
> EmployeeID CHAR(4) PRIMARY KEY,
> Employee VARCHAR(50),
> Position VARCHAR(50)
> )
>
> <ROOT>
> <Employees>
> <Employee ID="1212">Joe Smith</Employee>
> <Position>Sales Manager</Position>
> <Employee ID="1213">Bekky Carter</Employee>
> <Position>Sales Assistant</Position>
> </Employees>
> </ROOT>
>
| |
|
| This is very helpful, thankyou.
"Monica Frintu [MSFT]" wrote:
[color=darkred]
> Hello,
>
> Here is a sample schema:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:element name="ROOT" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Employees" sql:is-constant="true">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Employee" type="EmployeeType"
> sql:relation="Employees" sql:field="Employee"/>
> <xs:element name="Position" sql:relation="Employees"
> sql:field="Position"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
>
> <xs:complexType name="EmployeeType">
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="ID" sql:relation="Employees" sql:field="EmployeeID"/>
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> </xs:schema>
>
> But you'll end up with the following in your database:
>
> Joe Smith 1212 NULL
> NULL NULL Sales Manager
> Bekky Carter 1213 NULL
> NULL NULL Sales Assistant
>
> Hope this helps.
>
> Regards,
> --
> Monica Frintu
>
>
> "AndyH" wrote:
>
|
|
|
|
|