x

XSD structure and Table Structure

We get an XML file that I have to parse and store in a database. The values are coming in as a Name-Value pair instead of regular XML.

I do not want to store it as a name-value pair. The Start-Time and Stop-Time can be ignored (they repeat). I would rather build columns like CREDIT_RATING, UNIT_ARR_CREDIT_RATE, UNIT_CAP_CHRG_ATC_KWH, T01_UNIT_FIXED_ENRGY_COST_ATC to hold the values.

How do I go about building an XSD to use in SSIS for this?

<TRANSACTION TYPE="EXECUTED">
    <ROW TBL="CONTRACT">
        <CONTRACTID V="123456"/>
        <REVISION V="0"/>
        <SOURCESYSTEM V="xYz1"/>
        <STARTTIME V="06/01/2011 00:00:00"/>
        <STOPTIME V="05/31/2012 23:59:59"/>
        <EXECUTETIME V="04/06/2011 17:35:09"/>
        <SALESREPCODE V="100"/>
        <SALESREPNAME V="Mickey"/>
        <CONSULTANTCODE V="200"/>
        <CONSULTANTNAME V="Clubhouse"/>
    </ROW>
    <ROW TBL="CUSTOMER">
        <NAME V="Donald Associates"/>
        <LEGALNAME V="Donald Associates"/>
        <LEGALCONTACT V="LEGAL_CONTACT"/>
        <LEGALEMAIL V="LEGAL_EMAIL"/>
    </ROW>
    <ROW TBL="ACCOUNT">
        <OPCOCODE V="Orlando"/>
        <JURISCODE V="FL"/>
        <LDCACCOUNTNUMBER V="300"/>
        <NAME V="Donald Associates"/>
        <GLOBALID V="400"/>
        <BILLCYCLE V="10"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="ACCTDIRECTORY">
        <CONTACTTYPE V="SERVICE_ADDRESS"/>
        <ADDRESS1 V="4320 Acme Rd"/>
        <CITY V="FunTown"/>
        <STATE V="FL"/>
        <ZIP V="33333"/>
    </ROW>
    <ROW TBL="ACCTDIRECTORY">
        <CONTACTTYPE V="BILLING_ADDRESS"/>
        <ADDRESS1 V="c/o Minnie Mouse"/>
        <ADDRESS2 V="Billbox # 01-2345-6789"/>
        <ADDRESS3 V="P.O. Box 0000"/>
        <CITY V="HappiestPlace"/>
        <STATE V="CA"/>
        <ZIP V="11111"/>
    </ROW>
    <ROW TBL="ACCTDIRECTORY">
        <CONTACTTYPE V="MAILING_ADDRESS"/>
        <ADDRESS1 V="4320 Acme Rd"/>
        <CITY V="FunTown"/>
        <STATE V="FL"/>
        <ZIP V="33333"/>
    </ROW>
    <ROW TBL="EXEPROSPECT">
        <STORENUMBER V=""/>
    </ROW>
    <ROW TBL="METER">
        <SERIALNUMBER V="9876543210"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="RATE_CLASS"/>
        <VAL V="C31"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="CAPACITY"/>
        <VALNUM V="798.9261"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="NETWORK_SERVICES"/>
        <VALNUM V="804.0299"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="BILLING_OPTION"/>
        <VAL V="ONLY BILLING"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="BILLPRINT_OPTION"/>
        <VAL V="OPTION_1"/>
    </ROW>
    <ROW TBL="EEACCTATTRIBUTES">
        <ATTRIBUTECODE V="WEATHER_SENSITIVE"/>
        <VALNUM V="1"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTITEM">
        <CONTRACTID V="45454545"/>
        <REVISION V="0"/>
        <PRODUCT V="FULL_REQ"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="START_BILL_MONTH"/>
        <VALDATE V="06/01/2011 00:00:00"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="STOP_BILL_MONTH"/>
        <VALDATE V="06/01/2012 00:00:00"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="CONTRACT_PRODUCT"/>
        <VAL V="FULL_REQ"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="T01_CNSLT_FEE_OFF"/>
        <VALNUM V="0"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="T01_CNSLT_FEE_ON"/>
        <VALNUM V="0"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="CREDIT_RATING"/>
        <VAL V="AAA"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="UNIT_ARR_CREDIT_RATE"/>
        <VAL V="Y"/>
        <VALNUM V="-0.22"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="UNIT_CAP_CHRG_ATC_KWH"/>
        <VAL V="Y"/>
        <VALNUM V="0.00253"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="LSCMCONTRACTTERM">
        <TERMTYPECODE V="T01_UNIT_FIXED_ENRGY_COST_ATC"/>
        <VALNUM V="0.03378"/>
        <STARTTIME V="06/14/2011 00:00:00"/>
        <STOPTIME V="06/11/2012 23:59:59"/>
    </ROW>
    <ROW TBL="ANALYTICS">
        <PRICINGMODEL V="xYz1"/>
        <CRMOPPORTUNITYID V="5555555555555"/>
        <INTRVLAVAILIND V="Y"/>
        <EPHID V="OFFR-013058-01-0"/>
        <RRPTCALIBRATION V="\\server\share\folder1\folder2\Orlando\Calibration\2011-04-01_16-58"/>
        <CQCHANNEL V="40"/>
        <DELIVERYSERVICECLASS V="7"/>
    </ROW>
</TRANSACTION>
more ▼

asked Apr 13 '11 at 11:13 AM in Default

Raj More gravatar image

Raj More
1.7k 77 82 84

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I am (happily?) rubbish at XML so you may get a proper answer from someone else in due course but have you tried using SSIS or LogParser to handle the import?

SSIS allows you the option to generate an XSD from the source file.

LogParser may not need one.
more ▼

answered Apr 13 '11 at 01:09 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
We ended up using the XML Task and applied an XSLT (transform) to move the attributes to elements.
more ▼

answered Sep 01 '11 at 01:59 PM

Raj More gravatar image

Raj More
1.7k 77 82 84

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x898
x562
x136
x4

asked: Apr 13 '11 at 11:13 AM

Seen: 854 times

Last Updated: Apr 13 '11 at 11:13 AM