Design table to load data for XML file data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Design table to load data for XML file data

Author

Topic

pora
Starting Member

15 Posts

Posted - 2008-09-09 : 17:51:12

Hi,
I am new for SQL server and i need to load XML data file into sql server table.
I have not created table as i am confused it how to handle it.
If you show me the table design and procedure to load XMl data file, it will be appreciated.
Attached file are in following format:
DTrackSample.xml
DTrack.xsd
The reason i haven't mentioned here complete table structure because i am bit confused about the desing.
But my table will look like:


USER_PC_ID        PK1         
PRINTER_ID        PK2         
Printer_Flag                 
Printer_Version                  
USER_PC_OS_Version                  
OS_LOCALE                 
SKU                  
Printer_Start_Date                  
Printer_Stop_Date                  
Printed_Lables_Qty

They might be printing more than one per day so i need to capture how many times they print the job.
There pc will be unique means per pc, how many tiems they print the job.
I am also thinking to make printer_id as part of pk, because they might have mutiple printer attached within one pc
Please let me know if yo ucan help me out for table design also as data coming through XML file and i never done this kind of
design/loading for XML.

~~XML file:~~


<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xs:complexType name="StartStopStamp">
        <xs:attribute name="Start" type="xs:time" use="required"/>
        <xs:attribute name="Stop" type="xs:time" use="required"/>
    </xs:complexType>

    <xs:complexType name="LTrackInfo">
        <xs:attribute name="LType" type="xs:string" use="required"/>
        <xs:attribute name="PrintLCount" type="xs:int" use="required"/>
    </xs:complexType>

    <xs:complexType name="PTrackInfo">
        <xs:sequence>
            <xs:element name="Model" type="xs:string"/>
            <xs:element name="LocalFlag" type="xs:boolean"/>
            <xs:element name="LTrackInfo" type="LTrackInfo" minOccurs="1" maxOccurs="unbounded"/>
        </xs:sequence>
    </xs:complexType>

    <xs:complexType name="DTrackInfo">
        <xs:sequence>
            <xs:element name="DateStamp" type="xs:date"/>
            <xs:element name="PStartStop" type="StartStopStamp" minOccurs="1" maxOccurs="unbounded"/>
            <xs:element name="PTrackInfo" type="PTrackInfo" minOccurs="1" maxOccurs="unbounded"/>
        </xs:sequence>
    </xs:complexType>

    <xs:complexType name="DLTrackInfo">
        <xs:sequence>
            <xs:element name="DLVersion" type="xs:string"/>
            <xs:element name="DLDesh" type ="xs:string"/>
            <xs:element name="OSVersion" type="xs:string"/>
            <xs:element name="OSDesh" type ="xs:string"/>
            <xs:element name="PCId" type ="xs:string"/>
            <xs:element name="DTrackInfo" type ="DTrackInfo" minOccurs="0" maxOccurs="unbounded"/>
        </xs:sequence>

        <xs:attribute name="Version" type="xs:string" use="required"/>
    </xs:complexType>

    <xs:element name="DLTrackInfo" type="DLTrackInfo"/>
</xs:schema>

Sample XML Data File


<?xml version="1.0" encoding="utf-8"?>
<DLTrackInfo Version="1.0">
    <DLVersion>8.0.0.123</DLVersion>
    <DLLocale>en-US</DLLocale>
    <OSVersion>Windows XP 5.01.2600 Service Pack 2</OSVersion>
    <OSDesh>en-US</OSDesh>
    <PCId>1P64P021-2323-40d5-A0DA-064D4E162DPA</PCId>
    <DTrackInfo>
        <DateStamp>2008-09-01</DateStamp>
        <DStartStop Start="09:00:01" Stop="10:00:02"/>
        <DStartStop Start="14:58:59" Stop="16:42:42"/>
        <PTrackInfo>
            <Model>DM LWriter DUO Label</Model>
            <IsLocal>true</IsLocal>
            <LTrackInfo LType="Address" PLCount="10"/>
            <LTrackInfo LType="Shipping" PLCount="1"/>
        </PTrackInfo>
        <PTrackInfo>
            <Model>DM LWriter DUO Tape 128</Model>
            <IsLocal>true</IsLocal>
            <LTrackInfo LType="Tape24mm" PLCount="1"/>
        </PTrackInfo>
    </DTrackInfo>
    <DTrackInfo>
        <DateStamp>2008-09-04</DateStamp>
        <DStartStop Start="09:00:01" Stop="18:00:02"/>
        <PTrackInfo>
            <Model>DM LWriter DUO Label</Model>
            <IsLocal>true</IsLocal>
            <LTrackInfo LType="Address" PLCount="10"/>
            <LTrackInfo LType="Shipping" PLCount="1"/>
        </PTrackInfo>
        <PTrackInfo>
            <Model>DM LWriter DUO Label</Model>
            <IsLocal>false</IsLocal>
            <LTrackInfo LType="Address" PLCount="100"/>
        </PTrackInfo>
    </DTrackInfo>
</DLTrackInfo>

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources