Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Problem in SQL Server with the XML Bulk Load component

    I want create a mapping schema file in SQLXMLBulkLoad. My property XML have repeating tags below:

    <Images>
    <image id="Small Image">
    <url><![CDATA[98_1434442218_main.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="Large Image">
    <url><![CDATA[98_1434442218.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="1">
    <url><![CDATA[98_1434442218.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="2">
    <url><![CDATA[98_-1462313614.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="3">
    <url><![CDATA[98_-658384073.jpg]]></url>
    <title>
    </title>
    </image>
    </Images>

    Now i want to insert image1, image2,.... in different column in a table(such as Image1, Image2 etc). Also every properties have same images value.

    So how can i map this with database. What need to be write in schema file for this. What's is syntex? Waiting for reply.

    Best Regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One sample is no where near enough to analyze to determine how to map an XML schema to a database schema. If you knew more about the XML schema, it might be enough but by itself it isn't enough for more than a start.
    Quote Originally Posted by earthsantanu View Post
    I want create a mapping schema file in SQLXMLBulkLoad. My property XML have repeating tags below:
    Now i want to insert image1, image2,.... in different column in a table(such as Image1, Image2 etc). Also every properties have same images value.
    Not in the example that you posted... There are multiple image files.
    Quote Originally Posted by earthsantanu View Post
    So how can i map this with database.
    First truly understand your XML schema. Until you understand what you have now, there isn't any way to get anywhere else.
    Quote Originally Posted by earthsantanu View Post
    What need to be write in schema file for this.
    The answer to this depends on what is really in your XML schema and how you construct your database schema.
    Quote Originally Posted by earthsantanu View Post
    What's is syntex?
    Syntax is the "language" that the computer understands for describing your problem (and hopefully fixing it).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    <Property>
    <ID>2311988</ID>
    <Ref><![CDATA[23542598]]></Ref>
    <Name><![CDATA[]]></Name>
    <Url><![CDATA[http://www.themovechannel.com/property/details/2311988/]]></Url>
    <Date>2010-02-05</Date>
    <Country>England</Country>
    <Location1><![CDATA[Kent]]></Location1>
    <Location2><![CDATA[Maidstone]]></Location2>
    <Location3><![CDATA[]]></Location3>
    <Price>84995.00</Price>
    <Price_max>0.00</Price_max>
    <Currency>GBP</Currency>
    <Currencies>
    <Currency>
    <Name>EUR</Name>
    <Price>97976.95</Price>
    </Currency>
    <Currency>
    <Name>USD</Name>
    <Price>133451.09</Price>
    </Currency>
    <Currency Type="Local">
    <Name>GBP</Name>
    <Price>84995.00</Price>
    </Currency>
    </Currencies>
    <Sector>
    <en><![CDATA[Residential Property]]></en>
    </Sector>
    <Type>
    <en><![CDATA[Apartment]]></en>
    </Type>
    <SubType>
    <en><![CDATA[]]></en>
    </SubType>
    <Beds>1</Beds>
    <Beds_max>0</Beds_max>
    <Baths>1</Baths>
    <Attributes>
    <en>
    </en>
    </Attributes>
    <key_selling_points>
    <en>
    </en>
    </key_selling_points>
    <Short_Desc>
    <en><![CDATA[Your Move are delighted to offer for sale this one bedroom retirement flat situated in Manderville Court, which is within access of the town centre. The property comprises entrance hall, lounge, kitchen, bedroom and bathroom. Further benefits to note]]></en>
    </Short_Desc>
    <Full_Desc>
    <en><![CDATA[Your Move are delighted to offer for sale this one bedroom retirement flat situated in Manderville Court, which is within access of the town centre. The property comprises entrance hall, lounge, kitchen, bedroom and bathroom. Further benefits to note include double glazing where stated, storage heaters, communal residents area (including a sitting room), communal gardens and residents parking.<BR/>
    Accommodation comprising<BR/>
    DescriptionYour Move are delighted to offer for sale this one bedroom retirement flat situated in Manderville Court, which is within access of the town centre. The property comprises entrance hall, lounge, kitchen, bedroom and bathroom. Further benefits to note include double glazing where stated, storage heaters, communal residents area (including a sitting room), communal gardens and residents parking.<BR/>
    <BR/>
    Communal Entrance<BR/>
    <BR/>
    Entrance HallAiring cupboard with storage and water tank, electric storage heater.<BR/>
    <BR/>
    Lounge/Dining Room18' 0" x 9' 10"&nbsp; (5.49m x 3m)&nbsp;Double glazed window to rear, electric heater, telephone and tv point, two wall light points, sliding double doors to kitchen.<BR/>
    <BR/>
    Kitchen8' 3" x 5' 6"&nbsp; (2.51m x 1.68m)&nbsp;Measurements to include units. Double glazed window to rear, work surfaces with stainless steel sink and drainer, cupboard and drawer base units with space for washing machine, cooker and fridge/freezer, range of wall cupboards, tiling to walls.<BR/>
    <BR/>
    Bedroom11' 7" x 8' 8"&nbsp; (3.53m x 2.64m)&nbsp;Measurement plus door recess. Electric heater, tv point, telephone point, built in double wardrobe cupboard.<BR/>
    <BR/>
    BathroomWalk in bath, pedestal wash hand basin, low level WC, extractor fan, electric wall heater.<BR/>
    <BR/>
    Communal GardenTo the rear of the property, laid to lawn with seating area.<BR/>
    <BR/>
    Residents ParkingWe understand from the vendor that the property benefits from resident parking situated to the front of the property.<BR/>
    <BR/>
    Agents NoteIt is believed the property is Leasehold and you should refer to the Home Information Pack. Should you proceed this must be verified by your solicitor<BR/>
    <BR/>
    See Our Web Site For A Full Description Of This Property.<BR/>
    ]]></en>
    </Full_Desc>
    <Images>
    <image id="Small Image">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_1434442218_main.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="Large Image">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_1434442218.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="1">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_1434442218.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="2">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_-1462313614.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="3">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_-658384073.jpg]]></url>
    <title>
    </title>
    </image>
    <image id="4">
    <url><![CDATA[http://assets.fish4.co.uk/mmo/235/425/98_-1170285091.jpg]]></url>
    <title>
    </title>
    </image>
    </Images>
    <Units>
    </Units>
    <LinkType>
    </LinkType>
    <CPLFee>0.00</CPLFee>
    </Property>

    This is my feed sample of a property.

    CREATE TABLE [dbo].[import_porp_listing_temp] (
    [propId] [bigint] IDENTITY (1, 1) NOT NULL ,
    [projectId] [int] NOT NULL ,
    [datelisted] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [propname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [proptype] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [start_date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [end_date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [start_date2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [end_date2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [currency_type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [c_price] [money] NOT NULL ,
    [c_price2] [money] NOT NULL ,
    [long_primary_curency] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [long_monthly_rate] [money] NOT NULL ,
    [country1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [add1] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [postcode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [county] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [regstate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [plan_type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url_thumb] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url1] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg01] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url2] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg02] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url3] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg03] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url4] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg04] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url5] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg05] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url6] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg06] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url7] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg07] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url8] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg08] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url9] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg09] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [img_url10] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [descimg10] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [totfloorarea] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [floorareaunit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [totlandarea] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [landareaunit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [bedrooms] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [bathrooms] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [isOmit] [tinyint] NOT NULL ,
    [ownpool] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [off_build_date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [sleeps] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [other1] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [furnished] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_de] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_es] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_fr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_nl] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_it] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [description_ru] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PPL_Price] [money] NOT NULL
    )


    This is my table structure.
    I want to store ID, Name, Country, Location1, Location2, Location3, Price, Currency, Type, Beds, Baths, Full_Desc, Images in the table. The table has columns for storing these values like - propId, propname, country1, regstate, city, add1, c_price, currency_type, proptype, bedrooms, bathrooms, description. And for images there are some fields img_url_thumb, img_url1, descimg01, img_url2, descimg02 etc. Please tell me how can i construct the schema.

    Best Regards,

Tags for this Thread

Posting Permissions

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