May 24, 2012 at 2:34 pm
Hi, I have a table which has one column storing xml data.
I need to create a table of the xml data where one row is all of the XML data elements of one field value. So the new table would have exactly the same number of rows at the source table, but would have the union of all columns from the xml data. The only section I need is the <Data> ... </Data>
"EAT1" is column name with NULL value,
also
"ExtValue905" is column name with "TEST" as value
and so on.
The row key is "ActReqID" with value of "66601"
Thanks for the help...
Bill
Here is column contents (type : ntext)
<NewDataSet>
<Data>
<EAT1 />
<EAT3 />
<EAT4 />
<EAT9 />
<EAT24 />
<EAT29 />
<EAT30 />
<EAT31 />
<EAT33 />
<EAT34 />
<ExtValue905>TEST</ExtValue905>
<EAT909 />
<EAT924>AK</EAT924>
<ExtValue924>1305</ExtValue924>
<EAT2101 />
<EAT7801 />
<ExtValue7802>TEST</ExtValue7802>
<ExtValue7805>TEST</ExtValue7805>
<ExtValue7806>TEST</ExtValue7806>
<EAT7807 />
<EAT7808>Yes</EAT7808>
<ExtValue7808>11106</ExtValue7808>
<ExtValue7809>2008-11-18T00:00:00-05:00</ExtValue7809>
<EAT7810>Monday</EAT7810>
<ExtValue7810>11108</ExtValue7810>
<ExtValue7903>TEST</ExtValue7903>
<ExtValue7904>TEST</ExtValue7904>
<ExtValue7905>TEST</ExtValue7905>
<ExtValue7906>TEST</ExtValue7906>
<ExtValue7907>TEST</ExtValue7907>
<ActReqID>66601</ActReqID>
</Data>
<_x0037_818>
<encoded_list_item_id>11124</encoded_list_item_id>
<activity_id>0</activity_id>
<EAT7818>Flip Charts</EAT7818>
</_x0037_818>
<_x0037_818>
<encoded_list_item_id>11125</encoded_list_item_id>
<activity_id>0</activity_id>
<EAT7818>Podium</EAT7818>
</_x0037_818>
</NewDataSet>
May 24, 2012 at 3:31 pm
It would help immensely if you were to provide the DDL for the destination table, and a representative sample of the data that would reside in that table using the same data your XML example, i.e.
1. Provide a CREATE TABLE statement to show us the table you want the XML data inserted into
2. Provide a set of INSERT statements to fill the sample table with the values from the example XML exactly how you want them to exist in the table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 3:48 pm
Well, I do not know exactly what the table will be. This will need to a "select into table" defined by which rows are selected from the source table that contains the XML columns.
There is no hope that the data elements in the XML from one row will be the same as another. Some xml will have 50 elements others will have 75 and still another will have 100. This might result in a distinct list of elements (columns) of 120.
The hope was to learn how to create a query where each element in each XML could be processed so the table definition could be derirved, then created and then filled.
Tomorrow the xml data will likely be different in content.
Not alot of structure around this xml data.
So I was hoping to learn how to process this xml example and then I would apply to the others.
May 24, 2012 at 4:45 pm
I see. In that case I think the solution will require dynamic sql. Here is a start to help you get your column names. From here you can build a SELECT INTO statement dynamically that will create the table you are after with your data inserted:
DECLARE @xml AS XML;
SET @xml = '<NewDataSet>
<Data>
<EAT1 />
<EAT3 />
<EAT4 />
<EAT9 />
<EAT24 />
<EAT29 />
<EAT30 />
<EAT31 />
<EAT33 />
<EAT34 />
<ExtValue905>TEST</ExtValue905>
<EAT909 />
<EAT924>AK</EAT924>
<ExtValue924>1305</ExtValue924>
<EAT2101 />
<EAT7801 />
<ExtValue7802>TEST</ExtValue7802>
<ExtValue7805>TEST</ExtValue7805>
<ExtValue7806>TEST</ExtValue7806>
<EAT7807 />
<EAT7808>Yes</EAT7808>
<ExtValue7808>11106</ExtValue7808>
<ExtValue7809>2008-11-18T00:00:00-05:00</ExtValue7809>
<EAT7810>Monday</EAT7810>
<ExtValue7810>11108</ExtValue7810>
<ExtValue7903>TEST</ExtValue7903>
<ExtValue7904>TEST</ExtValue7904>
<ExtValue7905>TEST</ExtValue7905>
<ExtValue7906>TEST</ExtValue7906>
<ExtValue7907>TEST</ExtValue7907>
<ActReqID>66601</ActReqID>
</Data>
<_x0037_818>
<encoded_list_item_id>11124</encoded_list_item_id>
<activity_id>0</activity_id>
<EAT7818>Flip Charts</EAT7818>
</_x0037_818>
<_x0037_818>
<encoded_list_item_id>11125</encoded_list_item_id>
<activity_id>0</activity_id>
<EAT7818>Podium</EAT7818>
</_x0037_818>
</NewDataSet>'
SELECT Nodes.Name.query('local-name(.)')
FROM @xml.nodes('/NewDataSet/Data/*') AS Nodes (Name);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 6:35 am
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply