Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to select all elements of an XML document from an XML data type column Expand / Collapse
Author
Message
Posted Tuesday, September 07, 2010 11:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 3:13 PM
Points: 170, Visits: 335
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!



Post #981757
Posted Tuesday, September 07, 2010 11:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
What have you tried and how did the results differ from what you need?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #981777
Posted Tuesday, September 07, 2010 12:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 6,932, Visits: 12,665
davidsalazar01 (9/7/2010)
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!


Try one of the methods described in Jacob Sebastian's blog.
Since you didn't bother to provide any more detailed information that's about all I can tell you.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #981794
Posted Tuesday, September 07, 2010 1:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 3:13 PM
Points: 170, Visits: 335
John,

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!

David



Post #981840
Posted Tuesday, September 07, 2010 2:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
An 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?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #981909
Posted Tuesday, September 07, 2010 3:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 3:13 PM
Points: 170, Visits: 335
Hi 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.AmendBarsetFlightHistory

Thanks,

David



Post #981925
Posted Tuesday, September 07, 2010 3:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
OK, so how does this differ from what you expect?

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,
XMLAfterAction
FROM @AmendBarsetFlight






John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #981940
Posted Tuesday, September 07, 2010 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 6,932, Visits: 12,665
I 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.





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #981943
Posted Tuesday, September 07, 2010 3:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 3:13 PM
Points: 170, Visits: 335
Hi 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,

David



Post #981960
Posted Tuesday, September 07, 2010 4:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
Yes, 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?





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #981962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse