*Thanks* in advance...
Environment...SQL 2005...
I am working on an import/export XML process...
Here's my process...
Imported XML -> Import to SQL Import Working Table -> TSQL to Relational Tables to be modded by user(s) -> TSQL to SQL Export Working Table after mods -> TSQL to Export Relational Tables to go to XML -> Exported XML
The exported XML should have same format as original imported file with the modifications by users...
I am using XQuery with nodes, etc to get the data from an XML type field to disseminate to normal sql tables...
I am VERY new to XML so I don't know too much...I am not sure I am going in the right direction...
I am open to suggestions...I am looking at XML EXPLICIT without much luck...
Here's the issue...
Sample From XML File...
<variable ident="1" type="quantity">
<name>respid</name>*
<label>respid</label>*
<position start="1" finish="8" />
<values>*
<range from="00000001" to="99999999" />
</values>
</variable>
<variable ident="2" type="character">
<name>status</name>
<label>status</label>
<position start="9" finish="28" />
<size>20</size>
</variable>
<variable ident="3" type="character">
<name>email</name>
<label>email - background variable, to capture email address</label>
<position start="29" finish="178" />
<size>150</size>
</variable>
<variable ident="4" type="character">
<name>userid</name>
<label>userid - background variable, to capture userid</label>
<position start="179" finish="278" />
<size>100</size>
</variable>
<variable ident="6" type="single">
<name>q1a_1</name>
<label>Please select your model year - Model year</label>
<position start="280" finish="280" />
<values>
<value code="1">2003</value>
<value code="2">2004</value>
<value code="3">2005</value>
<value code="4">2006</value>
<value code="5">2007</value>
<value code="6">2008</value>
<value code="7">2009</value>
</values>
</variable>
<variable ident="7" type="single">
<name>q1b_1</name>
<label>Please select your brand - Brand</label>
<position start="281" finish="282" />
<values>
<value code="1">Aprilia</value>
<value code="2">Big Dog</value>
<value code="3">BMW</value>
<value code="4">Buell</value>
<value code="5">Can-Am</value>
<value code="6">Ducati</value>
<value code="7">Fischer</value>
<value code="8">Harley-Davidson</value>
<value code="9">Honda</value>
<value code="10">Hyosung</value>
<value code="11">Kawasaki</value>
<value code="12">KTM</value>
<value code="13">KYMCO</value>
<value code="14">Moto Guzzi</value>
<value code="15">MV Agusta</value>
<value code="16">Suzuki</value>
<value code="17">Triumph</value>
<value code="18">United Motors</value>
<value code="19">Victory</value>
<value code="20">Yamaha</value>
<value code="21">Other</value>
</values>
</variable>
I have created a set of relational tables where the *variable ident="7"*
which is an integer is the key or index on all tables...Where applicable, primary key, where not (ie. multiple recs per), indexed on...
The tables are:
XML_ExportVariable - 1 rec per variable element
XML_ExportName - 1 rec per variable element
XML_ExportLabel - 1 rec per variable element
XML_ExportPosition - 1 rec per variable element
XML_ExportSize - 1 rec per variable element
XML_ExportValue - n recs per variable element depending on value attribute...
My problems are with position, value elements...I can't seem to get the output right...
Here's my query but it's not producing correct output...
SELECT
ev.[variable!1!ident] AS 'variable/@ident',
ev.[variable!1!type] AS 'variable/@type',
evl.[values!5!from] AS 'variable/values/range/from',
evl.[values!5!to] AS 'variable/values/range/to',
evl.[values!5!code] AS 'variable/values/code',
evl.[values!5!value] AS 'variable/values',
en.[name!2!name] AS 'variable/name',
el.[label!3!label] AS 'variable/label',
ep.[position!4!start] AS 'variable/position/start',
ep.[position!4!end] AS 'variable/position/end',
es.[size!6!size] AS 'variable/size'
FROM
XML_Export_Variable ev
INNER JOIN
XML_Export_Name en
ON
ev.[variable!1!ident] = en.[name!2!ident]
INNER JOIN
XML_Export_Label el
ON
ev.[variable!1!ident] = el.[label!3!ident]
INNER JOIN
XML_Export_Position ep
ON
ev.[variable!1!ident] = ep.[position!4!ident]
INNER JOIN
XML_Export_Values evl
ON
ev.[variable!1!ident] = evl.[values!5!ident]
LEFT JOIN
XML_Export_Size es
ON
ev.[variable!1!ident] = es.[size!6!ident]
WHERE
en.[name!2!name] IS NOT NULL
ORDER BY
ev.[variable!1!ident]
FOR
XML PATH(''), TYPE, ELEMENTS
Sample Output:
Problem 1 - Same behaviour with different elements...
Output for value with from/to as elements instead of attributes of value tag...
Output for position with start/end elements but want to appear as attributes of position element...
Current:
<variable ident="1" type="quantity">
<name>respid</name>
<label>respid</label>
<values>
<range>
<from>00000001</from>
<to>99999999</to>
</range>
</values>
<position>
<start>1</start>
<end>8</end>
</position>
</variable>
Desired:
<variable ident="1" type="quantity">
<name>respid</name>
<label>respid</label>
<values>
<range from="00000001" to="99999999"</range>
</values>
<position start=1 end=8</position>
</variable>
Problem 2 - Multiple recs for multiple value codes...ie...Multiple...
Current (Note position issue as above):
<variable ident="6" type="single">
<name>q1a_1</name>
<label>Please select your model year - Model year</label>
<values>
<code>1</code>2003</values>
<position>
<start>280</start>
<end>280</end>
</position>
</variable>
<variable ident="6" type="single">
<name>q1a_1</name>
<label>Please select your model year - Model year</label>
<values>
<code>2</code>2004</values>
<position>
<start>280</start>
<end>280</end>
</position>
</variable>
<variable ident="6" type="single">
<name>q1a_1</name>
<label>Please select your model year - Model year</label>
<values>
<code>3</code>2005</values>
<position>
<start>280</start>
<end>280</end>
</position>
</variable>
Desired:
<variable ident="6" type="single">
<name>q1a_1</name>
<label>Please select your model year - Model year</label>
<values>
<value code="1">2003</value>
<value code="2">2004</value>
<value code="3">2005</value>
<value code="4">2006</value>
<value code="5">2007</value>
<value code="6">2008</value>
<value code="7">2009</value>
</values>
<position start="280" end="280"</position>
</variable>
And so on for any variable element that has multiple choices...
Thanks in advance...
JimiTheJett
Relational SQL Data to XML - Vet SQL/ASP - New XML
