November 21, 2003 at 3:10 am
Hi,
I'm doing a lot of SQL Server XML query writing these days and I'm having to do it all manually...
Does anybody know a tool that will let me build queries like this;
SELECT
1Tag,
nullParent,
id[Data!1!Id],
myfield1[Data!1!Field1],
myfield2[Data!1!Field2],
''[Param!2!Key],
''[Param!2!Value]
From
MyTable
UNION
SELECT
2Tag,
1Parent,
id[Data!1!Id],
''[Data!1!Field1],
''[Data!1!Field2],
T2.[Key][Param!2!Key],
T2.[Value][Param!2!Value]
FROM
MyTable2 T2, MyTable T1
WHERE
T1.id = T2.parent_id
ORDER BY
[Data!1!ID]
FOR XML EXPLICIT
What p's me off is that if I'm building a large XML dataset of say 15 union queries that I have to create all of the field aliases manually and if I then insert stuff that I have to go back and change it manually. Also if I get the data type wrong in a non-used part of the query for a given node, e.g., instead if trying to union a string with a string, it's a date with a string I have to manually go back through and correct my mistake (yes, I know, don't make the mistake in the first place).
Anybody know of such a tool or am I going to have to write one ?!
Cheers
Andy
November 24, 2003 at 8:00 am
This was removed by the editor as SPAM
December 11, 2003 at 6:49 am
I have used the free program FOAM to save much typing when there will be many UNIONs due to JOINs. You will need an example of the expected results to feed the app.
MISfIT
MISfIT
December 11, 2003 at 7:07 pm
xslt could be a way for the translation.
I am using sqlxml <sql:query> option and redirect output steam to xslt to get final result.
December 12, 2003 at 9:30 am
quote:
xslt could be a way for the translation.I am using sqlxml <sql:query> option and redirect output steam to xslt to get final result.
This seems like a good alternative. The XSLT syntax comes easier to me than FOR XML EXPLICIT, which I always wrestle with.
Is there a good way to do the XSLT transformation in SQL Server or do you have to do it in the application?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply