InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 04:12:34 GMT20http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxThanks for the reply. I used a another nasty SQL script to get the data I needed parsed from the XML docs that were stored in the table into another table so they could report on it. I appreciate your response.I've attached the "ParseEPOSFlightDetail.txt" file for your review.-David :-DFri, 03 Dec 2010 09:54:21 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxHey! Not sure if you still care, or if this is what you are looking for, but here it is anyway :)This assumes you know the root element name. You can store output in temp tables or table variables and process it any way you want. DECLARE @idoc INTDECLARE @exml XMLDECLARE @Root VARCHAR(64)SET @Root = '/YourRootName'SELECT @exml = [YourXMLColumn] FROM YourTableEXEC sp_xml_preparedocument @idoc OUTPUT, @exmlselect columnNames.id,columnNames.localname AS ELEMENT, columnValue.text AS VALUE FROM OPENXML (@idoc, @Root) columnNamescross apply (select text FROM OPENXML (@idoc, @Root) where nodetype = 3 AND parentid = columnNames.id) columnValuewhere columnNames.nodetype <> 3 and columnNames.localname not in ('xsi','xsd')Exec sp_xml_removedocument @idocFri, 03 Dec 2010 08:26:51 GMTmaciej_phttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxLutz/John,I've sent out a communication to the developer that requested this query to see if he can explain what needs to be shown in the result set. I'm awaiting a response. In the meantime I've attached the spec they sent me to get an idea of what they're trying to retrieve in the output to present in a Crystal report.Thanks guys!-DavidWed, 08 Sep 2010 11:00:19 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxLutz, I agree...please stay on. I know that you have ample XML experience and could add value to this thread. Thus far, I've been poking around to try to get an idea of what David really wants to see for output. From one of your previous posts, I think you are correct in thinking that David wants to combine the results from both colujmns (which could contain different elements).David, Big question here. You've shown us 1 row from your table, but I've yet to see an example of how you want your result set to look like. Based on the sample data that you've now given us, can you show us what the results should look like. Specifically, please combine the sample data that you've shown us into one result set and post that as an example of what you want your query to return. Telling us is not the same as showing us. Too much gets lost in the interpretation of the problem; whereas, a sample result set speaks for itself and we can more redily give you help. If needed, please read the article in the link from my signature line. It talks about properly setting up sample tables/data/results to get better/faster help.Wed, 08 Sep 2010 08:44:51 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspx@LutzM:The reply was actually directed to both you and John but I forgot to include your name on the posting as I was hurried to try to attach the XML docs and head out for the day to pick up my son from school. Please feel free to provide any more input and give any other suggestions as I'm really trying to get this resolved ASAP.Thanks guys! :-D-DavidTue, 07 Sep 2010 21:31:34 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspx@John:Since David addressing his issues implicit to your attention ignoring other posts, I leave this thread in your hands.Tue, 07 Sep 2010 16:43:16 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxHi John,I believe the output is fine from what I can tell as long as I can select all the elements from all the XML docs. Let me know if this will work based on the sample XML docs I've attached herein. In the sample docs I have selected the 1st row in the AmendBarsetFlightHistory table and displayed the XML document data that were in the columns (XMLBeforeAction and XMLAfterAction) as a reference of what I would like to select from this table. Let me know if this helps.Thanks!DavidTue, 07 Sep 2010 16:19:08 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxYes, that makes sense. My example shows the insert into the table simply to create sample data. Ignore that part and focus on the output. What I want to know is, based on the sample data in my example, how does the output shown differ from what you want to see?Tue, 07 Sep 2010 16:00:05 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxHi John,The code you provided is close to what I want for the requirement, except this code selects a new record into a table. I want to extract the existing XML documents from the table "AmendBarsetFlightHistory" that are currently stored in the XML data types columns ('XMLBeforeAction' and "XMLAfterAction") to output. Does that make sense? This is really confusing maybe because of the way I'm explaining it. Let me know if you need more clarification.Thanks,DavidTue, 07 Sep 2010 15:57:27 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxI might have a solution but I need some sample data to test it against.Especially, there's one issue that needs clarification: let's assume your first xml column has 5 nodes and your 2nd xml column has 4 nodes. How many rows do you expect as your final output? Since the nodes don't seem to have any relationship, I'd assume 20 rows. Please confirm/correct.Tue, 07 Sep 2010 15:24:45 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxOK, so how does this differ from what you expect?[code]DECLARE @AmendBarsetFlight TABLE (RowID int IDENTITY(1,1), XMLBeforeAction xml, XMLAfterAction xml)INSERT INTO @AmendBarsetFlight(XMLBeforeAction, XMLAfterAction)VALUES(' <xml_node> <test>XMLBeforeAction</test> </xml_node>', ' <xml_node> <test>XMLAfterAction</test> </xml_node>')SELECT XMLBeforeAction, XMLAfterActionFROM @AmendBarsetFlight[/code]Tue, 07 Sep 2010 15:18:30 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxHi John,I have a table called AmendBarsetFlight. Essentially, I just want to retrieve 2 XML data type columns called ('XMLBeforeAction' and 'XMLAfterAction'). I'm trying to extract the documents contained in the ('XMLBeforeAction' and 'XMLAfterAction') columns into 2 columns called the same in the query output within SQL. Here's an example I tried earlier, but it seems to not be working the way I want it to. The AmendBarsetFlight table has a little over 16k rows. When I ran this earlier it took a long time and didn't really show me any data (empty columns) in the output as I expected, so I had to cancel the query.SELECT XMLBeforeAction.query('data'), XMLAfterAction.query('data'), FROM dbo.AmendBarsetFlightHistoryThanks,DavidTue, 07 Sep 2010 15:06:14 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxAn example here would really help. Can you post an example of what the column data would be and how you expect your result set to look?Tue, 07 Sep 2010 14:46:52 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxJohn,I've tried to run several examples I've found online, but I'm not really getting the results I'm looking for since the query examples all select specific elements and I want to retrieve all elements in the xml document. I also want to select from 2 xml data type fields too in the same query too.Let me know if that helps or need more clarification.Thanks!DavidTue, 07 Sep 2010 13:19:39 GMTdavidsalazar01http://www.sqlservercentral.com/Forums/Topic981757-21-1.aspx[quote][b]davidsalazar01 (9/7/2010)[/b][hr]Hi All,Just need to select all XML elements from 2 different XML data type colums in the same table.Select XMLcolumn 1 and XML column 2 from 'table name'Thanks![/quote]Try one of the methods described in [b][url=http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx]Jacob Sebastian's blog[/url][/b].Since you didn't bother to provide any more detailed information that's about all I can tell you.Tue, 07 Sep 2010 12:01:39 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxWhat have you tried and how did the results differ from what you need?Tue, 07 Sep 2010 11:38:01 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic981757-21-1.aspxHi All,Just need to select all XML elements from 2 different XML data type colums in the same table.Select XMLcolumn 1 and XML column 2 from 'table name'Thanks!Tue, 07 Sep 2010 11:23:30 GMTdavidsalazar01