March 5, 2009 at 6:43 am
Hi All,
I have a requirement where in Say for ex:
** I have got a XML file structure ready say:
** I want to store the tags in database in some table.
** A Generic Stored Procedure which all fetch the tags from D/B, build the XML file as shown above and then store the Values in element by picking from some table and retrun the complete XML as string.
I need following help:
? Is its best practise or gud way to store the XML definiation in D/B as i mentioned.
?How can i best user of all the XML feature of SQL 2005 like having Nulll value in Tag etc.
? A code snippet of SP would be of great help.
THanks in advance
Robn
March 5, 2009 at 7:29 am
(1) We can't see your list of tags... please post them as an attachment.
(2) I'm not sure why you feel the need to store the tags. Build a temporary table with the proper column names, and tags are generated automatically by a FOR XML query.
Do you want to use this as a means of writing ad hoc queries, so that you change the XML and a query is automatically generated for you? If so, you are going to have to write and execute dynamic SQL, and it would probably be better if you stored the items you want as separate rows in a table, rather than as tags in an XML blob. If you will give us a little more information about what you are trying to accomplish, we can give you better advice and more specific answers. Thanks.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 5, 2009 at 9:19 am
You need to repost or edit your message to get the XML to show.
The SqlServerCentral message editor is not friendly towards XML.
To post your message, create it in another editor (NotePad will do).
Then replace all '<' characters by '<' and all '>' characters by '>'
So a line like:
<abc><def>test</def><def>again</def></abc>
Becomes
<abc><def>test</def><def>again</def></abc>
Finally, select the whole message, copy it and paste it into SSC's message box.
The XML should then display correctly.
Derek
March 5, 2009 at 9:24 am
Can't see your sample XML, but from the description of what you're talking about, you should take a look at "for XML" in Books Online. It's how you change relational data (tables with rows and columns) into XML data, in SQL Server. Pretty easy to use, once you read up on it and take a look at a few examples.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply