Results 1 to 3 of 3

Thread: db to xml

  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: db to xml

    hi,

    i have tables t1(t1_id, t2_id1, t2_id2) and t2(t2_id, t2_name), with t2_id1 and t2_id2 being foreign keys for t2_id.

    i want to display data from these tables in an xml file as follows:
    <t1_id>
    <t2_id1>t2_name</t2_id1>
    <t2_id2>t2_name</t2_id2>
    </t1_id>

    if i perform a: join on t1.t2_id1 = t2.t2_id or t1.t2_id2 = t2.t2_id it will duplicate rows if both t2_id1 and t2_id2 are in t2 table and i don't want that.

    [of course the tables are much more complicated so i am not allowed to make any changes to their design.]

    any idea on how to write a query, view to do this? all i want to do is to put desired data in the xml, in the most simple and efficient way.

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If duplicate rows is your main problem, you could use DISTINCT or rewrite your join as
    Code:
    select ...
    from t1
    join t2 
      on t1.t2_id1 = t2.t2_id 
    
    union
    
    select ...
    from t1
    join t2 
      on t1.t2_id2 = t2.t2_id
    
    order by t1.t1_id
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM t1
       JOIN t2
          ON (t2.doohicky1 = t1.doohicky1
          OR  t2.thingamabob2 = t1.thingamabob2)
       FOR XML
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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