views:

459

answers:

1

I have a SQL Server query that I need to convert to MySQL. I've never used SQL Server/T-SQL before, so I have no experience with FOR XML PATH. There's surprisingly little documentation on this sort of thing. If I remove the FOR XML PATH statement, MySQL returns the error "Operand should contain at least 1 column(s)."

It seems like the nested SELECT statements return strings containing raw XML data, but I don't have the original SQL Server database available to test that theory. I could emulate this effect easily if I knew the return schema.

The original query is below:

  SELECT
    har_autos.*, har_automodels.Model, har_automodels.MakeID, har_automakes.Make, har_autotypes.AutoType,
   (SELECT har_notes.*, har_notelinks.Value
        FROM har_notes
        JOIN har_notelinks on (har_notes.ID = har_notelinks.NoteID)
    WHERE har_autos.ID = har_notelinks.AutoID AND har_notes.NoteTypeID = 1)
    AS 'UserNotes',
   (SELECT har_notes.*, har_notelinks.Value
        FROM har_notes
        JOIN har_notelinks on (har_notes.ID = har_notelinks.NoteID)
    WHERE har_autos.ID = har_notelinks.AutoID AND har_notes.NoteTypeID = 2)
    AS 'EngineeringNotes'
FROM har_autos
LEFT JOIN har_automodels ON (har_autos.ModelID = har_automodels.ID)
LEFT JOIN har_automakes ON (har_automodels.MakeID = har_automakes.ID)
LEFT JOIN har_autotypes ON (har_autos.AutoTypeID = har_autotypes.ID)


UPDATE:

I rebuilt the query in its entirety and it now returns "Not unique table/alias: 'har_automakes'."

SELECT 
  har_autos.*,
  har_automodels.Model,
  har_automodels.MakeID,
  har_automakes.Make,
  har_autotypes.AutoType,
  (SELECT
     har_notes.ID,
     har_notes.NoteTypeID,
     har_notes.Text001,
     har_notelinks.Value,
     har_notelinks.AutoID,
     har_autos.ID
   FROM
     har_notes
     INNER JOIN har_notelinks ON (har_notes.ID = har_notelinks.NoteID),
     har_autos
   WHERE har_autos.ID = har_notelinks.AutoID AND har_notes.NoteTypeID = 1)
   AS UserNotes,
  (SELECT
     har_notes.ID,
     har_notes.NoteTypeID,
     har_notes.Text001,
     har_notelinks.Value,
     har_notelinks.AutoID,
     har_autos.ID
   FROM
     har_notes
     INNER JOIN har_notelinks ON (har_notes.ID = har_notelinks.NoteID), har_autos
   WHERE har_autos.ID = har_notelinks.AutoID AND har_notes.NoteTypeID = 2)
   AS EngineeringNotes
FROM
  har_autos
  LEFT OUTER JOIN har_automodels ON (har_autos.ModelID = har_automodels.ID)
  LEFT OUTER JOIN har_autotypes ON (har_autos.AutoTypeID = har_autotypes.ID),
  har_automakes
  LEFT OUTER JOIN har_automakes ON (har_automakes.MakeID = har_automakes.ID)
WHERE
  ID = 1


This is the original T-SQL query (really sorry about all of these):

        SELECT a.*, mo.Model, mo.MakeID, ma.Make, at.AutoType,
            (SELECT n.*, nl.Value
             FROM dbo.Notes n
             JOIN dbo.NoteLinks nl on (n.ID = nl.NoteID)
                WHERE a.ID = nl.AutoID AND n.NoteTypeID = 1
                FOR XML PATH('Note'), TYPE
                ) AS 'UserNotes',
            (SELECT n.*, nl.Value
             FROM dbo.Notes n
             JOIN dbo.NoteLinks nl on (n.ID = nl.NoteID)
                WHERE a.ID = nl.AutoID AND n.NoteTypeID = 2
                FOR XML PATH('Note'), TYPE
                ) AS 'EngineeringNotes'
        FROM dbo.Autos a
                LEFT JOIN dbo.AutoModels mo ON (a.ModelID = mo.ID)
                LEFT JOIN dbo.AutoMakes ma ON (mo.MakeID = ma.ID)
                LEFT JOIN dbo.AutoTypes at ON (a.AutoTypeID = at.ID)
+1  A: 

Little Documentation:

All of the above are from the following search: http://social.technet.microsoft.com/Search/en-US/?Refinement=129&Query=for+xml+path.

John Saunders
Thank you! That third link seems to be what I'm looking for. I found all the others from searching around. I had an issue with `CONCAT` truncating data, however (ala MySQL's documentation on generating XML via query).
sli
I added the query built with SQL Studio's query builder, and the original T-SQL query that I need to port.
sli