XML XSD Schema

Question

XML XSD Schema

TriggerMe

SSChampion

Points: 11752
More actions
June 2, 2004 at 7:39 pm

#102046

Somebody please explain to me how to create a XML XSD Schema for 2 tables in a SQL Server Database? I need to do this to be able to use the SQLXMLBulkLoad utility.
Kindest Regards,
June 7, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

jmblock1 SSC Veteran Points: 230 More actions · Answer 1

The specification for an XSD can be found at: http://www.w3.org/XML/Schema

Microsoft extends this for use with its parsers and SQL Server: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_SQLXML.asp

I have yet to find a single tool at generates even a usuable XSD for even a simple schema, but XMLSpy seems to get the closest. Since you are using more than one table, you need to be very carefull to pay attention to the relationships and keys.

btoum.roumada SSC Enthusiast Points: 159 More actions · Answer 2

OK, I don't know if this will help, as it uses an annotated XDR-schema. I'm just going to reiterate an example from Professional SQL Server 2000 XML, Wrox 2001 (ISBN:1861005466). This example shows how the Northwind Order and Order_Detail tables can be bulk loaded in one pass. You can download this code off the http://www.wrox.com website.

Data file:

<ROOT>

<Orders>

<OrderID>12000</OrderID>

<CustomerID>AROUT</CustomerID>

<EmployeeID>9</EmployeeID>

<OrderDate>2001-04-07</OrderDate>

<RequiredDate>2001-04-30</RequiredDate>

<ShipVia>4</ShipVia>

<Freight>40.00</Freight>

<ShipName>Around The Horn</ShipName>

<ShipAddress>120 Hanover Sq</ShipAddress>

<ShipCity>London</ShipCity>

<ShipPostalCode>WA1 1DP</ShipPostalCode>

<ShipCountry>UK</ShipCountry>

<OrderDetails>

<ProductID>4</ProductID>

<UnitPrice>22.00</UnitPrice>

<Quantity>100</Quantity>

</OrderDetails>

<OrderDetails>

<ProductID>5</ProductID>

<UnitPrice>21.35</UnitPrice>

<Quantity>100</Quantity>

</OrderDetails>

</Orders>

</ROOT>

XDR file:

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="OrderID" dt:type="int" />

<ElementType name="CustomerID" dt:type="int" />

<ElementType name="EmployeeID" dt:type="int" />

<ElementType name="OrderDate" dt:type="date" />

<ElementType name="RequiredDate" dt:type="date" />

<ElementType name="ShipVia" dt:type="int" />

<ElementType name="Freight" dt:type="r8" />

<ElementType name="ShipName" dt:type="string" />

<ElementType name="ShipAddress" dt:type="string" />

<ElementType name="ShipCity" dt:type="string" />

<ElementType name="ShipPostalCode" dt:type="string" />

<ElementType name="ShipCountry" dt:type="string" />

<ElementType name="ProductID" dt:type="int" />

<ElementType name="UnitPrice" dt:type="r8" />

<ElementType name="Quantity" dt:type="int" />

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

<element type="Orders" />

</ElementType>

<ElementType name="Orders" sql:relation="Orders" >

<element type="OrderID" sql:field="OrderID" />

<element type="CustomerID" sql:field="CustomerID" />

<element type="EmployeeID" sql:field="EmployeeID" />

<element type="OrderDate" sql:field="OrderDate" />

<element type="RequiredDate" sql:field="RequiredDate" />

<element type="ShipVia" sql:field="ShipVia" />

<element type="Freight" sql:field="Freight" />

<element type="ShipName" sql:field="ShipName" />

<element type="ShipAddress" sql:field="ShipAddress" />

<element type="ShipCity" sql:field="ShipCity" />

<element type="ShipPostalCode" sql:field="ShipPostalCode" />

<element type="ShipCountry" sql:field="ShipCountry" />

<element type="OrderDetails" >

<sql:relationship

key-relation="Orders"

key="OrderID"

foreign-key="OrderID"

foreign-relation="[Order Details]" />

</element>

</ElementType>

<ElementType name="OrderDetails" sql:relation="[Order Details]" >

<element type="ProductID" sql:field="ProductID" />

<element type="UnitPrice" sql:field="UnitPrice" />

<element type="Quantity" sql:field="Quantity" />

</ElementType>

</Schema>

TriggerMe SSChampion Points: 11752 More actions · Answer 3

Thanks to the both of you, I will give it a go. Seems like XML is still in its infantry stages as there are no decent tools for XML.

Kindest Regards,

btoum.roumada SSC Enthusiast Points: 159 More actions · Answer 4

You never asked if there were any tools that helped in creating bulk-load schemas; In which case, use Altova Mapforce/XMLSpy in conjunction with the SQLXML documentation.

Trial version is available at http://www.xmlspy.com