Creating Parent ID into tabel from XML into SQL

[Ronsol]

Hi,

I want to import a XML file into SQL.
But i can't find a good example to create an unique id from an higer element.
I can create different tables, but a link between them not. I want to create an ID of the parent.
Can somebody help me with a correct mapping schema?

The xml file is :
[HTML]
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Export>
<msg-out priority="normal" confirmation="no">
<resources>
<resource>
<id>CWws0086</id>
<code>86</code>
<name>Jansen</name>
<resource_kind>
<code>MWK</code>
<name>Motorwagen box</name>
<resource_tag>2</resource_tag>
</resource_kind>
<active>true</active>
<division>
<code>1</code>
<name>Transport</name>
</division>
</resource>
</resources>
</msg-out>
</Export>

[/HTML]
So i can create 2 tabels "Resource ( with fields id, code,name) and Resource_kind. (code,name,resource_tag) with 2 mapping scrips.
But no relation of them.
Can somebody help me with it and also to create everything in 1 script?

This is one script i already have :
[HTML]
<?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="id" dt:type="string" />
<ElementType name="code" dt:type="string" />
<ElementType name="name" dt:type="string" />
<ElementType name="active" dt:type="string" />
<ElementType name="resource_tag" dt:type="string" />


<ElementType name="Export" sql:is-constant="1"></ElementType>
<ElementType name="msg-out" sql:is-constant="1"></ElementType>
<ElementType name="resources" sql:is-constant="1">
<element type="resource" />
</ElementType>

<ElementType name="resource" sql:relation="Msg_resource">
<element type="id" sql:field="Id" />
<element type="code" sql:field="code" />
<element type="name" sql:field="Name" />
<element type="active" sql:field="active" />
</ElementType>

</Schema>
[/HTML]
Thxs

[Dököll]

Hiya Ronsol!

I just posted the below and thought you also might find the second helpful: http://www.w3schools.com/xquery/xquery_add.asp

http://www.w3schools.com/dom/dom_nodes_set.asp

In a bit and welcome...