Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > XML > XML
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
XML General XML discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XML section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 4th, 2008, 05:15 PM
Registered User
 
Join Date: Dec 2008
Location: Los Angeles, CA, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Relational SQL Data to XML - Vet SQL/ASP - New XML

*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
Reply With Quote
  #2 (permalink)  
Old December 4th, 2008, 06:06 PM
Registered User
 
Join Date: Dec 2008
Location: Los Angeles, CA, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Update to post...Now I have everything working but the multiple rows issue...

Here's the query...

SELECT
    ev.[variable!1!ident] AS 'variable/@ident',
    ev.[variable!1!type] AS 'variable/@type',
    en.[name!2!name] AS 'variable/name',
    el.[label!3!label] AS 'variable/label',
    CAST(evl.[values!5!code] AS INT) AS 'variable/values/@code',
    evl.[values!5!value] AS 'variable/values',
    evl.[values!5!from] AS 'variable/values/range/@from',
    evl.[values!5!to] AS 'variable/values/range/@to',
    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
FOR
    XML PATH(''), TYPE, ELEMENTS

Current Output:

<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="1">2003</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="2">2004</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="3">2005</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="4">2006</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="5">2007</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="6">2008</values>
  <position start="280" end="280" />
</variable>
<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="7">2009</values>
  <position start="280" end="280" />
</variable>

Desired Output:

<variable ident="6" type="single">
  <name>q1a_1</name>
  <label>Please select your model year - Model year</label>
  <values code="1">2003</values>
  <values code="2">2004</values>
  <values code="3">2005</values>
  <values code="4">2006</values>
  <values code="5">2007</values>
  <values code="6">2008</values>
  <values code="9">2009</values>
  <position start="280" end="280" />
</variable>

Thanks in advance...

JTJ

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Force SQL to update the data from an xml file ivanv SQL Server 2005 0 April 23rd, 2007 04:07 AM
IS There A Way TO Download Data F Sql To Xml File alyeng2000 ASP.NET 1.0 and 1.1 Professional 4 April 28th, 2005 09:22 AM
retrieve data from SQL server wih XML Thebravehearth XML 3 October 7th, 2004 02:07 AM
XML from SQL Server data spinout XML 5 September 25th, 2004 05:18 AM
inser xml data to sql server from asp ak Classic ASP Databases 7 February 25th, 2004 01:05 PM



All times are GMT -4. The time now is 01:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.