Post reply

Import XML into SQL table shema

  • August 26, 2010 at 9:26 am

    #100300

    I would like to import XML data into SQL server table with SQL Builk Load metod, but I'm wery new in XML. I nead help to prepare XML shema.

    Here is part of my XML file:

    <?xml version="1.0" encoding="windows-1250" standalone="yes" ?>

    <firm name="X-Name">

    <prices>

    <group>Cards</group>

    <secgroup>Video Cards</secgroup>

    <item>

    <desc>ATI 1</desc>

    <id>0040307</id>

    <price>0,80</price>

    </item>

    <item>

    <desc>ATI 2</desc>

    <id>0040308</id>

    <price>0,111</price>

    </item>

    </prices>

    </firm>

    I think the structure of XML is wery simple. Problem is "Item" group.

    Please help. Thanks.

  • August 26, 2010 at 1:12 pm

    #1212509

    Let's assume you have the file stored as c:\Temp\SSC.txt.

    Then you could use the following code (assuming you'd like to store the data in a xml column):

    /*

    CREATE TABLE T (IntCol int, XmlCol xml)

    */

    INSERT INTO T(XmlCol)

    SELECT * FROM OPENROWSET(

    BULK 'c:\Temp\SSC.txt',

    SINGLE_BLOB) AS x

    SELECT

    c.value('@name[1]','varchar(10)') AS firm_name,

    v.value('group[1]','varchar(10)') AS Grp,

    v.value('secgroup[1]','varchar(10)') AS SecGroup,

    y.value('desc[1]','varchar(10)') AS descr,

    y.value('id[1]','varchar(10)') AS id,

    y.value('price[1]','varchar(10)') AS price

    FROM T

    CROSS APPLY XmlCol.nodes('firm') a(c)

    CROSS APPLY a.c.nodes('prices') U(v)

    CROSS APPLY U.v.nodes('item') X(y)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 26, 2010 at 2:16 pm

    #1212550

    Thanks to respond, but I think you don't understand me.

    I need to make XSD shema mapping file to import XML data to SQL Table. I use VB.

    I already made mapping file like this:

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

    <xsd:element name="firm" sql:is-constant="1">

    <xsd:complexType>

    <xsd:sequence>

    <xsd:element name="prices" sql:is-constant="1">

    <xsd:complexType>

    <xsd:sequence>

    <xsd:element name="item" sql:relation="TestTable" maxOccurs="unbounded">

    <xsd:complexType>

    <xsd:sequence>

    <xsd:element name="desc" sql:field="desc" type="xsd:string" />

    <xsd:element name="id" sql:field="id" type="xsd:string" />

    <xsd:element name="price" sql:field="price" type="xsd:float" />

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    </xsd:schema>

    But this mapping shema not work well. I dont know how to include definitions for "group" and "secgroup" in table I get NULL value. If I change underline element name to "prices" and include "group" and "secgroup" elements, "item" elements don't work (I get NULL in SQL table).

    TestTable Structure is:

    group varchar(100)

    secgroup varchar(100)

    desc varchar(250)

    id varchar(10)

    price real

    If mapp file work for "Prices" level, don't work on "item", if work on "item", do't work for "Prices". Thats the problem.

  • August 26, 2010 at 2:40 pm

    #1212557

    Why do you have to use VB? Pass the path of your xml file to a stored procedure and let SQL Server do the mapping.

    But if you really want to add the VB overhead and your xml schema, you'd need to reflect the nesting level of the nodes in your xsd as well.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 26, 2010 at 2:46 pm

    #1212561

    Lutz, you done it again! I've learned two things from your post:

    1. I thought that you had to reference the nodes as a.c.value, etc. in the column list area of the select. Didn't know you could just use c.value.

    2. I never thought about cross applying a previous .nodes() into a new one. I've always done:

    FROM @XmlCol.nodes('firm') a(c)

    CROSS APPLY @XmlCol.nodes('firm/prices') U(v)

    CROSS APPLY @XmlCol.nodes('firm/prices/item') X(y)

    Thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • August 26, 2010 at 3:06 pm

    #1212570

    WayneS (8/26/2010)

    Lutz, you done it again! I've learned two things from your post:

    1. I thought that you had to reference the nodes as a.c.value, etc. in the column list area of the select. Didn't know you could just use c.value.

    2. I never thought about cross applying a previous .nodes() into a new one. I've always done:

    FROM @XmlCol.nodes('firm') a(c)

    CROSS APPLY @XmlCol.nodes('firm/prices') U(v)

    CROSS APPLY @XmlCol.nodes('firm/prices/item') X(y)

    Thanks!

    :blush: I'm sorry.

    Regarding #1:

    Basically you can compare my notation as using unqualified column declaration (like SELECT MyColumn From MyTable T) vs. yours as using qualified column declaration (like SELECT T.MyColumn From MyTable T). Guess which one should be preferred... So you're doing it the proper way. The reason that it CAN be done doesn't imply it SHOULD be done. 😉

    Regarding #2:

    I've never done any test to compare the performance of the two methods so I cannot say whether it's better/equal/worse. I might do some testing just out of curiosity.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 27, 2010 at 1:20 am

    #1212771

    You right LutzM.

    Today I will try to implement your solution and later I will modify all existing imports. I think is much better. No nead to distribute "sqlxml.msi" to client PC's, performance is better, all is in one place, ...

    One think more. What about "attributes", how to import attributes, for example from my test XML a "name" attribute from "firm" element.

    And maybe if someone know how to prepare mapping shema for my example it would be nice to have solution here. Maybe someone one day nead. I looking on net many hours and I don't find nothing usable. I think my example is simple but it seems not.

  • August 27, 2010 at 1:36 am

    #1212783

    Sorry LutzM, I didn't look well. I see now that problem with attribute "name" is already solved.

    Thanks for your solution 🙂

    Uros

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply