November 28, 2003 at 3:16 am
Hi,
Firstly, hope you don't mind this post here as I've had no replies in the XML forum;
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
1 Tag,
null Parent,
id [Data!1!Id],
myfield1 [Data!1!Field1],
myfield2 [Data!1!Field2],
'' [Param!2!Key],
'' [Param!2!Value]
From
MyTable
UNION
SELECT
2 Tag,
1 Parent,
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
December 2, 2003 at 8:00 am
This was removed by the editor as SPAM
December 3, 2003 at 9:31 am
Havent seen one, though that doesnt mean there isnt one. IMO it's not worth the effort, easier to pull it back as a resultset and do something with it on the middle tier.
Andy
December 3, 2003 at 10:09 am
I have to add also that after microsoft realized that processing XML on the back end could not be as efficient as processing it in the middle tier they included the Client_Side Flag on the Web release!
* Noel
December 8, 2003 at 8:49 pm
Andy,
I too wish there were an easier way to do this as my SP that uses the Explicit XML conversion is almost 3000 lines long! Trying to debug it is a nightmare! Unfortunately I don't know of anyway you would be able to acurately create a query builder for this type of query.
One thing I do is to make sure that I format my SQL such that I have all my node names aliased with an = rather than a space... IE:
SELECT
Tag = 1
, Parent = NULL
, [Data!1!id] = id
, ....
In this way I can then cut and paste the nodes into the next union statement and just change the fields after the = sign. It took me about an hour to rewrite the old SP when I converted it to this format. But last time I had to add another node it was much easier!
This works especially well when you use an editor that allows columnar selection and deletion like Visual Studio. With it you can simply highlight everything after the = and delete it in one keystroke. Then you know when you have forgotten to redo the node with the correct information.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply