Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: convert xml to db

    dear all,

    i have an XML that i want to create a database for it, so depending on this XML i need to create the right tables to store the information in tables and then call these info from the database instead of from XML.

    it is a big XML but here is just one part, the other parts are the same:

    <?xml version="1.0" encoding="UTF-8" ?>
    - <RENTETARIEVEN date="04-12-2007 00:00">
    - <GELDVERSTREKKER mvcode="AA" naam="ABN AMRO">
    - <HYPVORM mvcode="aflosvrij">
    - <PRODUCT mvcode="2000" mvproductcode="197" naam="Aflossingsvrije hypotheek" renteaangepast="4-10-2007">
    - <VARIABEL>
    <NHG>5.4</NHG>
    <EW ew="75">5.6</EW>
    <EW ew="90">5.9</EW>
    <EW ew="100">5.9</EW>
    <EW ew="125">5.9</EW>
    </VARIABEL>
    + <VAST periode="1">
    + <VAST periode="2">
    + <VAST periode="3">
    + <VAST periode="5">
    + <VAST periode="6">
    + <VAST periode="7">
    + <VAST periode="10">
    + <VAST periode="12">
    + <VAST periode="15">
    + <VAST periode="20">
    + <VAST periode="25">
    + <VAST periode="30">
    + <PRODUCT mvcode="XX" mvproductcode="9045" naam="Internet hypotheek" overbrugging="6.6" renteaangepast="3-12-2007">
    + <HYPVORM mvcode="annuiteit">
    + <PRODUCT mvcode="1000" mvproductcode="286" naam="Annuïteitenhypotheek" renteaangepast="4-10-2007">
    - <VARIABEL>
    <NHG>5.4</NHG>
    <EW ew="75">5.6</EW>
    <EW ew="90">5.9</EW>
    <EW ew="100">5.9</EW>
    <EW ew="125">5.9</EW>
    </VARIABEL>
    - <VAST periode="1">
    <NHG>5.3</NHG>
    <EW ew="75">5.5</EW>
    <EW ew="90">5.6</EW>
    <EW ew="100">5.6</EW>
    <EW ew="125">5.8</EW>
    </VAST>
    - <VAST periode="2">
    <NHG>5.3</NHG>
    <EW ew="75">5.5</EW>
    <EW ew="90">5.6</EW>
    <EW ew="100">5.6</EW>
    <EW ew="125">5.8</EW>
    </VAST>
    - <VAST periode="3">
    <NHG>5.3</NHG>
    <EW ew="75">5.5</EW>
    <EW ew="90">5.6</EW>
    <EW ew="100">5.6</EW>
    <EW ew="125">5.8</EW>
    </VAST>
    - <VAST periode="5">
    <NHG>5.3</NHG>
    <EW ew="75">5.5</EW>
    <EW ew="90">5.6</EW>
    <EW ew="100">5.6</EW>
    <EW ew="125">5.8</EW>
    </VAST>
    - <VAST periode="6">
    <NHG>5.3</NHG>
    <EW ew="75">5.5</EW>
    <EW ew="90">5.6</EW>
    <EW ew="100">5.6</EW>
    <EW ew="125">5.8</EW>
    </VAST>
    - <VAST periode="7">
    <NHG>5.4</NHG>
    <EW ew="75">5.6</EW>
    <EW ew="90">5.7</EW>
    <EW ew="100">5.7</EW>
    <EW ew="125">5.9</EW>
    </VAST>
    - <VAST periode="10">
    <NHG>5.4</NHG>
    <EW ew="75">5.6</EW>
    <EW ew="90">5.7</EW>
    <EW ew="100">5.7</EW>
    <EW ew="125">5.9</EW>
    </VAST>
    - <VAST periode="12">
    <NHG>5.5</NHG>
    <EW ew="75">5.7</EW>
    <EW ew="90">5.8</EW>
    <EW ew="100">5.8</EW>
    <EW ew="125">6</EW>
    </VAST>
    - <VAST periode="15">
    <NHG>5.6</NHG>
    <EW ew="75">5.8</EW>
    <EW ew="90">5.9</EW>
    <EW ew="100">5.9</EW>
    <EW ew="125">6.1</EW>
    </VAST>
    - <VAST periode="20">
    <NHG>5.7</NHG>
    <EW ew="75">5.9</EW>
    <EW ew="90">6</EW>
    <EW ew="100">6</EW>
    <EW ew="125">6.2</EW>
    </VAST>
    - <VAST periode="25">
    <NHG>5.7</NHG>
    <EW ew="75">5.9</EW>
    <EW ew="90">6</EW>
    <EW ew="100">6</EW>
    <EW ew="125">6.2</EW>
    </VAST>
    - <VAST periode="30">
    <NHG>5.8</NHG>
    <EW ew="75">6</EW>
    <EW ew="90">6.1</EW>
    <EW ew="100">6.1</EW>
    <EW ew="125">6.3</EW>
    </VAST>
    </PRODUCT>
    </HYPVORM>
    - <HYPVORM mvcode="beleggers">
    + <PRODUCT mvcode="XX" mvproductcode="9050" naam="Internet hypotheek" overbrugging="6.6" renteaangepast="3-12-2007">
    + <VARIABEL>
    + <HYPVORM mvcode="hybride">
    + <PRODUCT mvcode="4000" mvproductcode="2316" naam="Meegroeihypotheek" renteaangepast="4-10-2007">
    + <HYPVORM mvcode="lineair">
    - <PRODUCT mvcode="6000" mvproductcode="2509" naam="Lineaire hypotheek" renteaangepast="4-10-2007">
    + <VARIABEL>
    + <HYPVORM mvcode="spaar">
    - <PRODUCT mvcode="7000" mvproductcode="3110" naam="Meegroeihypotheek" renteaangepast="4-10-2007">
    + <PRODUCT mvcode="7109" mvproductcode="7687" naam="Spaargroeihypotheek" renteaangepast="4-10-2007">
    + <HYPVORM mvcode="tradleven">
    + <PRODUCT mvcode="5000" mvproductcode="3214" naam="Levensverzekeringhypotheek" renteaangepast="4-10-2007">
    + <HYPVORM mvcode="unitlink">
    + <PRODUCT mvcode="8000" mvproductcode="1486" naam="Meegroeihypotheek" renteaangepast="4-10-2007">
    </PRODUCT>
    </HYPVORM>
    </GELDVERSTREKKER>

    i already started creating some tables but i need to know some good practice to o this. any help will be very appriciated


    thanks
    Peace out
    Mr joka

  2. #2
    Join Date
    Mar 2008
    Location
    Earth
    Posts
    3

    Wink convert xml to db

    well..if you want to import the xml data into your database table then this example can guide you-

    DECLARE @idoc int

    DECLARE @doc varchar(8000)

    --sample XML document

    SET @doc ='
    <ROOT>
    - <TRAVEL_ITEM>
    <LEGACY_REF_KEY>100461</LEGACY_REF_KEY>
    <TRAVEL_ITEM_TYPE_CODE>LMRCH</TRAVEL_ITEM_TYPE_CODE>
    <ITEM_NAME>Gari Melchers home and Studio at Belmont</ITEM_NAME>
    - <CLASSES>
    - <CLASS>
    <CLASS_DESC>Entertainment and Attractions</CLASS_DESC>
    <SUB_CLASS_DESC>Attractions, Zoos, Museums, and Tours</SUB_CLASS_DESC>
    </CLASS>
    </CLASSES>
    - <GENS>
    - <GEN>
    <CLUB_CODE>212</CLUB_CODE>
    <PHYSICAL_LINE1_ADR>224 WASHINGTON STREET</PHYSICAL_LINE1_ADR>
    <PHYSICAL_CITY_NAME>FREDERICKSBURG</PHYSICAL_CITY_NAME>
    <PHYSICAL_STATE_PROVINCE_CODE>VA</PHYSICAL_STATE_PROVINCE_CODE>
    <PHYSICAL_ZIP_CODE>22405</PHYSICAL_ZIP_CODE>
    <PHYSICAL_COUNTRY_CODE>USA</PHYSICAL_COUNTRY_CODE>
    <DIRECT_PHONE>(540) 654-1015</DIRECT_PHONE>
    </GEN>
    </GENS>
    - <PARENT_TRAVEL_ITEMS>
    - <PARENT_TRAVEL_ITEM>
    <PARENT_ITEM_NAME>Gari Melchers Home &amp; Studio at Belmont</PARENT_ITEM_NAME>
    <PARENT_LEGACY_REF_KEY>4049</PARENT_LEGACY_REF_KEY>
    <PARENT_TRAVEL_ITEM_TYPE_CODE>MRCH</PARENT_TRAVEL_ITEM_TYPE_CODE>
    </PARENT_TRAVEL_ITEM>
    </PARENT_TRAVEL_ITEMS>
    - <TEXTS>
    - <TEXT>
    <TEXT_TYPE_CODE>ITT_OFFER_TYPE</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Other</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>ITT_SERVICE_DESCRIPTION</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Art Museum and Historic Home</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>OFFER_TYPE</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Other</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>OFFER_DESCRIPTION</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Save $1 on admission.</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>ITT_METHOD_OF_BUSINESS</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Store Front</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>METHOD_OF_BUSINESS</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Store Front</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>ITT_OFFER_DESCRIPTION</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Save $1 on admission.</PARAGRAPH_TEXT>
    </TEXT>
    - <TEXT>
    <TEXT_TYPE_CODE>SERVICE_DESCRIPTION</TEXT_TYPE_CODE>
    <PARAGRAPH_TEXT>Art Museum and Historic Home</PARAGRAPH_TEXT>
    </TEXT>
    </TEXTS>
    </TRAVEL_ITEM>
    </ROOT>
    '
    -- Create an internal representation of the XML document.

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    -- Execute a SELECT statement using OPENXML rowset provider.

    SELECT * into XML_Table2

    FROM OPENXML (@idoc,N'/ROOT/TRAVEL_ITEM')

    WITH (
    LEGACY_REF_KEY varchar(255)'LEGACY_REF_KEY',

    TRAVEL_ITEM_TYPE_CODE varchar(255) 'TRAVEL_ITEM_TYPE_CODE',

    ITEM_NAME varchar(255) 'ITEM_NAME',

    CLASS_DESC varchar(255) ' CLASSES/CLASS/CLASS_DESC',

    SUB_CLASS_DESC varchar(255) ' CLASSES/CLASS/SUB_CLASS_DESC',

    CLUB_CODE varchar(255) ' GENS/GEN/CLUB_CODE',

    PHYSICAL_LINE1_ADR varchar(255) ' GENS/GEN/PHYSICAL_LINE1_ADR',

    PHYSICAL_CITY_NAME varchar(255) ' GENS/GEN/PHYSICAL_CITY_NAME',

    PHYSICAL_STATE_PROVINCE_CODE varchar(255) ' GENS/GEN/PHYSICAL_STATE_PROVINCE_CODE',

    PHYSICAL_ZIP_CODE varchar(255) ' GENS/GEN/PHYSICAL_ZIP_CODE',

    PHYSICAL_COUNTRY_CODE varchar(255) ' GENS/GEN/PHYSICAL_COUNTRY_CODE',

    DIRECT_PHONE varchar(255) ' GENS/GEN/DIRECT_PHONE',

    PARENT_ITEM_NAME varchar(255) ' PARENT_TRAVEL_ITEMS/PARENT_TRAVEL_ITEM/PARENT_ITEM_NAME',

    PARENT_LEGACY_REF_KEY varchar(255) ' PARENT_TRAVEL_ITEMS/PARENT_TRAVEL_ITEM/PARENT_LEGACY_REF_KEY',

    PARENT_TRAVEL_ITEM_TYPE_CODE varchar(255) ' PARENT_TRAVEL_ITEMS/PARENT_TRAVEL_ITEM/PARENT_TRAVEL_ITEM_TYPE_CODE',

    TEXT_TYPE_CODE varchar(255) ' TEXTS/TEXT/TEXT_TYPE_CODE',

    PARAGRAPH_TEXT varchar(255) ' TEXTS/TEXT/PARAGRAPH_TEXT'
    )


    EXEC sp_xml_removedocument @idoc
    Last edited by tyagi_master; 04-30-08 at 17:09.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    speaking of xml, I found this funny:

    http://thedailywtf.com/Articles/Oh,-XML.aspx

  4. #4
    Join Date
    Mar 2008
    Location
    Earth
    Posts
    3
    can v load the above xml file in to SQL Server 2000 table using BCP??
    if no...why?
    if yes..how?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •