Hello,
I have a table in a database, one of the column is a string that
contains xml data. the xml contains meta data that I'll need to
process and query at any time from the dataset.
Any idea how to do it?
Do I need first to query the database and fill the dataset, read it
line by line put the sting into xml document etc... ? or there is a
much better and elegant way.
Is there a way to map all the xml rows returned into a relational db schema?
The xml in the string is not the same and could be one of different xml variants. |
Request for Question Clarification by
mathtalk-ga
on
29 Dec 2004 06:43 PST
Hi, eyal32ny-ga:
Does the column in question contain a complete (well-formed) XML
document, or is the content only an "XML fragment"?
If the contents are simple enough, you may be able to extract and
analyze the "meta data" you want with some SQL "string" functions,
esp. if you put this task into a stored procedure.
regards, mathtalk-ga
|
Clarification of Question by
eyal32ny-ga
on
29 Dec 2004 08:07 PST
more details:
The table is a transaction history table, means all activities are
logged there. The activity is logged based on a time stamp and each
row is one activity. I need to create a daily log per user of these
activities. The userID and it's activites are part of the xml.
The question is what would be the best way to perform it.
Loading the xml into a dataset , and using an xpath to find the
information I need? Will I be able to do it in one pass for all users?
Is there a way to do this query on the db level and generate the daily
log per user from the database? I am warea of "FOR XML AUTO, XMLDATA"
that returns an xml document of the results set but it may get too
messy.
result of querying this column may yield 10k rows, few of them are:
<createAction Usr="a" Epid="0" url="a://create/newentity">
<Properties createText="test" RefUrl="http://test.html text2="test2" />
</createAtion>
<createAction Usr="c" Epid="0" url="c://create/newentity">
<Properties createText="test" RefUrl="http://test.html text2="test2" />
</createAtion>
<createAction Usr="b" Epid="0" url="b://create/newentity">
<Properties createText="test" RefUrl="http://test.html text2="test2" />
</createAtion>
<copyRequest url="a://copy/xxxx" Usr="a" />
<moveRequest url="a://move/y" Usr="a" />
|
Request for Question Clarification by
mathtalk-ga
on
29 Dec 2004 09:25 PST
Right, the FOR XML option formats your SQL query results into an XML
result, which doesn't particularly help if you're trying to avoid
parsing XML results on the client side of things.
You said the result of querying this column "may yield 10k rows", but
I think the question of greater interest is how big the value in the
column for one row will be.
regards, mathtalk-ga
|
Clarification of Question by
eyal32ny-ga
on
29 Dec 2004 10:12 PST
If I understand you right, I dont think that value per row of this
column will have more then 1500 characters.
|
Request for Question Clarification by
mathtalk-ga
on
29 Dec 2004 11:50 PST
Is the column a varchar or text field?
thanks, mathtalk-ga
|
Clarification of Question by
eyal32ny-ga
on
29 Dec 2004 12:15 PST
the type of the column is a user defined type named T_NXML with length
of 16. when I looked at the definition I found that this user defined
type is a "ntext" with length 16. does it mean anything to you? sorry
I cannot give you more information, I am not that familiar with the
db.
|
Request for Question Clarification by
mathtalk-ga
on
29 Dec 2004 12:54 PST
Thanks, it means that it's a text field that allows Null values.
If I understand correctly, for each row in the table, the column will
contain either zero or one "action", but these may be expressed in
"variant" XML tags. You listed:
<createAction></createAction>
<copyRequest/>
<moveRequest/>
and there may be others. But all of these have a characteristic
attribute "Usr" which you need to extract/count.
regards, mathtalk-ga
|
Clarification of Question by
eyal32ny-ga
on
29 Dec 2004 13:01 PST
you understood it right but one thing. there are other attributes I'll
need to process. "usr" is the attribute I'll need to sort the logs by.
note:
some activity has "InnerXml" which is another node with its own
attributes that I'll have to process as well.
|
Request for Question Clarification by
mathtalk-ga
on
29 Dec 2004 14:34 PST
Hi, eyal32ny-ga:
Thanks for the prompt Clarification. I'm 95% sure you can use a
stored procedure that goes through the appropriate records (ones for a
particular day) and reports the counts by user that you want. I don't
have enough information to write the exact code you'll need, but I
could provide a code snippet based on the XML fragment samples you
showed (some of which have an "inner" element, as you noted).
Depending on your own coding skills, you might prefer to code this in
ado.net and C#. I can show you how to do it that way, using the
regular expression classes from the .Net framework. If your SQL
Server box is loaded, performance might be better managed by
offloading this task to another machine (for the actual counting), but
there may be some synergies to a mixed strategy. For example, if
there is a significant fraction of "action" records from "users" that
are not to be counted (eg. maybe a "stay alive" test user account),
you might use the SQL approach to eliminate some of the rows at the
server.
Despite the XML tags being used, I'm not sure that there's much point
to using an XML parser & XPath to locate the information. I guess it
would be more attractive to do it that way if the "action" strings
were more deeply nested and variable in their individual structures.
The single "action" tag _could_ serve as the root element of an XML
document (just add the prolog for each row).
From the (admittedly small) sample you showed, I'd recommend just
"brute force" string searching (with the reg. exp. classes in .Net).
Either way it will be interesting to try and keep the code "generic"
as you cover all the possible action types.
regards, mathtalk-ga
|
Clarification of Question by
eyal32ny-ga
on
30 Dec 2004 05:56 PST
Hi,
If you can provide me your email address, I'll send you a sample of
the data, and the ADO.NET code I wrote.
I am not looking for a count of users but rather to send each user its
actions. (formatted based on some xml structure that has nothing to do
with the xml in the column)
the xml provide the action description but other columns has the data
of timestamp and actionResult (ok/failed)
I would be interested to see both ways of implementation, directly on
db server as stored procedure, and via ado.net (which make me assume
will have few inner loops)
Thanks for your help
Eyal
|
Request for Question Clarification by
mathtalk-ga
on
30 Dec 2004 10:11 PST
Unfortunately Researchers are not allowed to have direct contact with
you by the Terms of Service for Google Answers. I suggest you post a
list of the actions and metadata for each that would be of interest,
grouping them if possible into cases that are similar.
Let me correct/amplify something I wrote previously. The datatype
ntext has length 16 because it is effectively a pointer to a
(potentially very big) string stored apart from the rows in the table.
The "n" prefix means that the characters are treated as Unicode, and
not that Null values are permitted. I suspect that a text field
inherently allows Nulls in MS SQL Server.
regards, mathtalk-ga
|
