Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Parsing Xml With Openxml

    <getHotels>

    <result>
    <address>Rupertiweg 3</address>

    <checkin>
    <from>14:00</from>
    <to>20:00</to>
    </checkin>

    <checkout>
    <from>08:00</from>
    <to>11:00</to>
    </checkout>
    <city>Kleinarl</city>
    <city_id>-3794558</city_id>
    <class>4</class>
    <class_is_estimated>0</class_is_estimated>
    <commission>0</commission>
    <countrycode>at</countrycode>
    <currencycode>EUR</currencycode>
    <district/>
    <hotel_id>71178</hotel_id>
    <hoteltype_id>14</hoteltype_id>
    <is_closed>0</is_closed>

    <location>
    <latitude>47.2676982509523</latitude>
    <longitude>13.3250427246094</longitude>
    </location>
    <maxrate>310</maxrate>
    <minrate>100</minrate>
    <name>Hotel Landhaus Lärchenhof</name>
    <nr_rooms>12</nr_rooms>
    <preferred>0</preferred>
    <ranking>29013</ranking>
    <review_nr>5</review_nr>
    <review_score>7.9</review_score>

    <url>
    Booking.com: Hotel Landhaus Lärchenhof, Kleinarl, Austria. Book your hotel now!
    </url>
    <zip>5603</zip>
    </result>
    </getHotels>
    I am trying store the above XML data into the SQL SERVER. The code in the stored procedure is as below

    ALTER PROCEDURE [dbo].[sp_InsertHotels]
    @xmlContent XML
    AS
    BEGIN
    DECLARE @XmlDocumentHandle int

    EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @xmlContent

    DECLARE @errorCode INT

    DECLARE @address NVARCHAR(255)
    DECLARE @checkin_from NVARCHAR(255)
    DECLARE @checkin_to NVARCHAR(255)
    DECLARE @checkout_from NVARCHAR(255)
    DECLARE @checkout_to NVARCHAR(255)
    DECLARE @city_id INT
    DECLARE @city NVARCHAR(255)
    DECLARE @class_is_estimated BIT
    DECLARE @class CLASS
    DECLARE @commission_obsolete REAL
    DECLARE @countrycode COUNTRYCODE
    DECLARE @currencycode CURRENCYCODE
    DECLARE @district NVARCHAR(255)
    DECLARE @hotel_id INT
    DECLARE @hoteltype_id INT
    DECLARE @is_closed BIT
    DECLARE @location_latitude REAL
    DECLARE @location_longitude REAL
    DECLARE @maxrate REAL
    DECLARE @minrate REAL
    DECLARE @name NVARCHAR(255)
    DECLARE @nr_rooms INT
    DECLARE @policygroup_id INT
    DECLARE @preferred BIT
    DECLARE @ranking INT
    DECLARE @review_nr INT
    DECLARE @review_score NVARCHAR(255)
    DECLARE @url NVARCHAR(255)
    DECLARE @zip NVARCHAR(255)


    SET @name = N'%'

    --I create a cursor to iterate over the developer items in the XML file
    DECLARE developerCursor CURSOR FOR
    SELECT address, checkin_from, checkin_to, checkout_from, checkout_to, city_id, city, class_is_estimated, class, commission_obsolete, countrycode, currencycode, district, hotel_id, hoteltype_id, is_closed, location_latitude, location_longitude, maxrate, minrate, name, nr_rooms, policygroup_id, preferred, ranking, review_nr, review_score, url, zip
    FROM OPENXML (@XmlDocumentHandle, '/getHotels/result',2)
    WITH
    (
    address NVARCHAR(255),
    checkin_from NVARCHAR(255),
    checkin_to NVARCHAR(255),
    checkout_from NVARCHAR(255),
    checkout_to NVARCHAR(255),
    city_id INT,
    city NVARCHAR(255),
    class_is_estimated BIT,
    class class,
    commission_obsolete REAL,
    countrycode countrycode,
    currencycode currencycode,
    district NVARCHAR(255),
    hotel_id INT,
    hoteltype_id INT,
    is_closed BIT,
    location_latitude REAL,
    location_longitude REAL,
    maxrate REAL,
    minrate REAL,
    name NVARCHAR(255),
    nr_rooms INT,
    policygroup_id INT,
    preferred BIT,
    ranking INT,
    review_nr INT,
    review_score NVARCHAR(255),
    url NVARCHAR(255),
    zip NVARCHAR(255)
    )

    OPEN developerCursor
    FETCH NEXT FROM developerCursor INTO @address, @checkin_from, @checkin_to, @checkout_from, @checkout_to, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location_latitude, @location_longitude, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip

    --SET @checkin_from = @checkout('from')

    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO tbl_Hotels (address, checkin_from, checkin_to, checkout_from, checkout_to, city_id, city, class_is_estimated, class, commission_obsolete, countrycode, currencycode, district, hotel_id, hoteltype_id, is_closed, location_latitude, location_longitude, maxrate, minrate, name, nr_rooms, policygroup_id, preferred, ranking, review_nr, review_score, url, zip)
    VALUES (@address, @checkin_from, @checkin_to, @checkout_from, @checkout_to, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location_latitude, @location_longitude, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip)
    FETCH NEXT FROM developerCursor INTO @address, @checkin_from, @checkin_to, @checkout_from, @checkout_to, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location_latitude, @location_longitude, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip
    END

    CLOSE_RESOURCES_DEVELOPER:

    CLOSE developerCursor

    DEALLOCATE developerCursor

    -- Remove the internal representation.
    EXEC sp_xml_removedocument @XmlDocumentHandle
    END
    Here I have a problem. In the database NULL value is getting stored for the following columns.


    checkin_from
    checkin_to
    checkout_from
    checkout_to
    location_latitude
    location_longitude

    This is due to XML ELEMENTS (<checkin>, <checkout>, <location>) been multivalued, and my above stored proc code is not able to parse these elements.

    Now can any one please let me the solution as how to resolve this issue.

    Thanks in advance.

    This is quite urgent....


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    This is bit urgent. Kindly guide me through....

    Thanks,
    Rahul Jha

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Guys, I need an urgent help from you all..... Kindly have a thought on this.....


    Thanks,
    Rahul Jha

  4. #4
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Guys, I found the solution with the below steps..

    1. READING the <checkin>, <checkout>, <location> tags as XML
    2. PARSING the above XMLs by OPENXML

    Below the the new stored procedure that works now.

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go



    ALTER PROCEDURE [dbo].[sp_InsertHotels]
    @xmlContent XML
    AS
    BEGIN
    DECLARE @XmlDocumentHandle INT
    DECLARE @XmlDocumentHandleCheckin INT
    DECLARE @XmlDocumentHandleCheckout INT
    DECLARE @XmlDocumentHandlelocation INT

    EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @xmlContent

    DECLARE @errorCode INT

    DECLARE @address NVARCHAR(255)
    DECLARE @checkin XML
    DECLARE @checkin_from NVARCHAR(255)
    DECLARE @checkin_to NVARCHAR(255)
    DECLARE @checkout XML
    DECLARE @checkout_from NVARCHAR(255)
    DECLARE @checkout_to NVARCHAR(255)
    DECLARE @city_id INT
    DECLARE @city NVARCHAR(255)
    DECLARE @class_is_estimated BIT
    DECLARE @class CLASS
    DECLARE @commission_obsolete REAL
    DECLARE @countrycode COUNTRYCODE
    DECLARE @currencycode CURRENCYCODE
    DECLARE @district NVARCHAR(255)
    DECLARE @hotel_id INT
    DECLARE @hoteltype_id INT
    DECLARE @is_closed BIT
    DECLARE @location XML
    DECLARE @location_latitude REAL
    DECLARE @location_longitude REAL
    DECLARE @maxrate REAL
    DECLARE @minrate REAL
    DECLARE @name NVARCHAR(255)
    DECLARE @nr_rooms INT
    DECLARE @policygroup_id INT
    DECLARE @preferred BIT
    DECLARE @ranking INT
    DECLARE @review_nr INT
    DECLARE @review_score NVARCHAR(255)
    DECLARE @url NVARCHAR(255)
    DECLARE @zip NVARCHAR(255)


    SET @name = N'%'

    --I create a cursor to iterate over the developer items in the XML file
    DECLARE developerCursor CURSOR FOR
    SELECT address, checkin, checkout, city_id, city, class_is_estimated, class, commission_obsolete, countrycode, currencycode, district, hotel_id, hoteltype_id, is_closed, location, maxrate, minrate, name, nr_rooms, policygroup_id, preferred, ranking, review_nr, review_score, url, zip
    FROM OPENXML (@XmlDocumentHandle, '/getHotels/result',2)
    WITH
    (
    address NVARCHAR(255),
    checkin XML,
    --checkin_from NVARCHAR(255) '@from',
    --checkin_to NVARCHAR(255) '@to',
    checkout XML,
    --checkout_from NVARCHAR(255) '@from',
    --checkout_to NVARCHAR(255) '@to',
    city_id INT,
    city NVARCHAR(255),
    class_is_estimated BIT,
    class class,
    commission_obsolete REAL,
    countrycode countrycode,
    currencycode currencycode,
    district NVARCHAR(255),
    hotel_id INT,
    hoteltype_id INT,
    is_closed BIT,
    location XML,
    --location_latitude REAL,
    --location_longitude REAL,
    maxrate REAL,
    minrate REAL,
    name NVARCHAR(255),
    nr_rooms INT,
    policygroup_id INT,
    preferred BIT,
    ranking INT,
    review_nr INT,
    review_score NVARCHAR(255),
    url NVARCHAR(255),
    zip NVARCHAR(255)
    )

    OPEN developerCursor
    FETCH NEXT FROM developerCursor INTO @address, @checkin, @checkout, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC sp_xml_preparedocument @XmlDocumentHandleCheckin OUTPUT, @checkin
    SELECT @checkin_from = [from], @checkin_to = [to]
    FROM OPENXML (@XmlDocumentHandleCheckin, '/checkin', 2)
    WITH
    (
    [from] VARCHAR(50),
    [to] VARCHAR(50)
    )


    EXEC sp_xml_preparedocument @XmlDocumentHandleCheckout OUTPUT, @checkout
    SELECT @checkout_from = [from], @checkout_to = [to]
    FROM OPENXML (@XmlDocumentHandleCheckout, '/checkout', 2)
    WITH
    (
    [from] VARCHAR(50),
    [to] VARCHAR(50)
    )

    EXEC sp_xml_preparedocument @XmlDocumentHandlelocation OUTPUT, @location
    SELECT @location_latitude = latitude, @location_longitude = longitude
    FROM OPENXML (@XmlDocumentHandlelocation, '/location', 2)
    WITH
    (
    latitude VARCHAR(50),
    longitude VARCHAR(50)
    )

    -- Remove the internal representation.
    EXEC sp_xml_removedocument @XmlDocumentHandleCheckin
    EXEC sp_xml_removedocument @XmlDocumentHandleCheckout
    EXEC sp_xml_removedocument @XmlDocumentHandlelocation

    INSERT INTO tbl_Hotels (address, checkin_from, checkin_to, checkout_from, checkout_to, city_id, city, class_is_estimated, class, commission_obsolete, countrycode, currencycode, district, hotel_id, hoteltype_id, is_closed, location_latitude, location_longitude, maxrate, minrate, name, nr_rooms, policygroup_id, preferred, ranking, review_nr, review_score, url, zip)
    VALUES (@address, @checkin_from, @checkin_to, @checkout_from, @checkout_to, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location_latitude, @location_longitude, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip)

    FETCH NEXT FROM developerCursor INTO @address, @checkin, @checkout, @city_id , @city, @class_is_estimated, @class, @commission_obsolete, @countrycode, @currencycode, @district, @hotel_id, @hoteltype_id, @is_closed, @location, @maxrate, @minrate, @name, @nr_rooms, @policygroup_id, @preferred, @ranking, @review_nr, @review_score, @url, @zip
    END

    CLOSE_RESOURCES_DEVELOPER:

    CLOSE developerCursor

    DEALLOCATE developerCursor

    -- Remove the internal representation.
    EXEC sp_xml_removedocument @XmlDocumentHandle
    END

    Thanks,
    Rahul Jha

Posting Permissions

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