Post reply

Parsing XML file

  • July 14, 2011 at 5:41 pm

    #241501

    I have an XML file in the following format and want to import it into the following table. How do I do this?

    CREATE TABLE LookupTable (TableName varchar(128), ColumnName varchar(128), LookupValue varchar(255))

    <?xml version="1.0" encoding="UTF-8">

    <lookupsEntry table="L_RACETYPES">

    <value column="RACETYPE">6</value>

    <value column="RACECODE">0</value>

    <value column="RACEDESC">Other than listed race</value>

    <value column="RACERANK" />

    </lookupsEntry>

  • July 15, 2011 at 3:06 am

    #1353254

    Something like this?

    SELECT

    T.c.value('@table[1]','VARCHAR(30)') as TableName,

    U.v.value('@column[1]','VARCHAR(30)') as ColumnName,

    U.v.value('.[1]','VARCHAR(30)') as LookupValue

    FROM @xml.nodes('lookupsEntry') T(c)

    CROSS APPLY T.c.nodes('value') U(v)


    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]

  • July 15, 2011 at 8:55 am

    #1353472

    You rock! That worked perfectly.

    Thank you.

  • July 15, 2011 at 9:21 am

    #1353491

    Glad I could help 😀


    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]

Viewing 4 posts - 1 through 3 (of 3 total)

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