August 24, 2009 at 7:56 am
I have a column in a table which contains XML , eed to shred the xml into columns and rows as "filed" are repeated in the xml and nee to fetch values for those fields.... here is the XML structure
August 24, 2009 at 8:55 am
August 24, 2009 at 10:39 am
Thanks..... But the problem is i do not know how many rows are there in my table coulmn and I have to shred
August 24, 2009 at 11:02 am
Rows or columns?
You don't know about the table or the XML? I'm confused.
August 24, 2009 at 11:12 am
The XML I placed here is in a column of the table. Now when I have to shred this my result would look like this...
memberid accountinfo dateofbirth -------> All te Columns Like this
-------------------- ----------- ----------------------
11111 10 01/01/1978 ----> All the row should have data
2222 15 02/01/1088
August 24, 2009 at 1:55 pm
It sounds like your XML has a self-describing section. In other words - there's a section explaining the data layout, and one for the data itself.
In most cases you will need to take a couple of passes through the XML. First one would be to build the table structure needed to take in the data (based on the FIELDS Section you have), and the second one to performan an insert (takng from the xml.nodes() parts of the various rows). Start here for a primer on how to do that:
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
(look over the NODES example with CROSS APPLY)
If your data is going to continually change, you're gong to need some reasonably fancy dynamic SQL handling to make it work automated.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 3:11 pm
I'd recommend to shred the xml into a table with three columns (rowID, Field, Value) and either use a cross tab or a dynamic cross tab with this table.
If there are more than one "target rows" (e.g. more fields nodes for addtl. rows) you should consider adding a unique attribute per fields element to make shredding the xml structure easier (I've used rowID in the table mentioned above). If that's not possible, you'd have to add this sequence number while shredding.
In order to give you an example containing your sample data I'd like you to provide a more descriptive sample as well as expected result (especially with reference to the number of drug/action/savings being fixed or flexible an to the option of adding an attribute to the fields element).
August 24, 2009 at 3:43 pm
My purpose is to flatten the XML as table with rows and columns. In my sample xml above all the nodes "name" will be treated as columns and node "value" will be treated as rows.
data after extraction should look like this....
memberid accountno birthdate xyz---- Other columns
---------- ---------- ----------- ----- ---------------
1111111 77767676 01/01/2005 yyy kjkkkkk
1111112 77767677 01/01/2003 yyy kjkkkkk
August 24, 2009 at 3:55 pm
RV (8/24/2009)
My purpose is to flatten the XML as table with rows and columns. In my sample xml above all the nodes "name" will be treated as columns and node "value" will be treated as rows.data after extraction should look like this....
memberid accountno birthdate xyz---- Other columns
---------- ---------- ----------- ----- ---------------
1111111 77767676 01/01/2005 yyy kjkkkkk
1111112 77767677 01/01/2003 yyy kjkkkkk
Based on your sample data given in the first post I don't think it's possible to come up with the expected result shown above:
There are no memberid's '1111111' or '1111112' nor do any other values match your sample data.
It would be really helpful if your sample data and your expected result actually would correlate...
August 24, 2009 at 4:20 pm
Okay Here is the sample data from the above XML should look like....
memberid accountno birthdate
---------- ---------- -----------
REH000 11/29/1944
Like above there are other filed "name" in the xml and the field "value" which should be flattened into table with column and rows like mentioned here.
Thanks
August 24, 2009 at 4:37 pm
I still have to recommend you're trying to provide sample data that do reflect your situation.
Let me repeat my recommendation from earlier in this thread:
In order to give you an example containing your sample data I'd like you to provide a more descriptive sample as well as expected result (especially with reference to the number of drug/action/savings being fixed or flexible and to the option of adding an attribute to the fields element).
Also, if your real data have more than one memberid, why isn't it reflected within your sample data?
Do you have one row per memberid in your xml column or do you basically have one row with the xml data holding more than one memberid?
You need to help us help you...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply