Results 1 to 3 of 3

Thread: OpenXML

  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: OpenXML

    Hi

    I've run into a problem using openxml and would greatly appreciate any help or direction!

    The Xml document I am working with contains the following excerpt:

    HTML Code:
    <Address>
            <UKAddress>
    		<no>24</no>
                    <Line>A</Line>
    		<Line>B</Line>
                    <PostCode>GIR 0AA</PostCode>
    	</UKAddress>
    </Address>
    My problem is this: There can be a variable number of line elements (between 2 and 5), and in addition each element tag is identical to the others, i.e. Line.

    I am trying to populate a SQL Server 2000 table (which contains the columns AddressLine1, AddressLine2, etc.) using OpenXML

    Here's the code for my sproc....

    .
    ...
    .
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT *
    FROM OPENXML (@idoc, 'root/data/', 2)
    WITH (Number int '//no',
    // address line detail here
    Postcode varchar(10) '//PostCode')
    .
    ...
    .

    I'm ok with OpenXML/sp_xml_preparedocument syntax and that, but I can't think how to handle unknown multiple address lines with the same tag name!

    ANY HELP GREATLY RECEIVED!!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    only thing that I can say is that whatever builds the XML, the need to supply all the tags that match your definition
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2007
    Posts
    4

    Problem Solved!!

    For anyone who might stumble upon this, I found the answer here.

    http://msdn2.microsoft.com/en-us/library/ms256086.aspx

    y/x[i]

Posting Permissions

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