SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to select all elements of an XML document from an XML data type column


Need to select all elements of an XML document from an XML data type column

Author
Message
davidsalazar01
davidsalazar01
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 374
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!



John Rowan
John Rowan
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21480 Visits: 4600
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
LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38145 Visits: 13559
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
davidsalazar01
davidsalazar01
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 374
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



John Rowan
John Rowan
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21480 Visits: 4600
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
davidsalazar01
davidsalazar01
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 374
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



John Rowan
John Rowan
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21480 Visits: 4600
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
LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38145 Visits: 13559
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
davidsalazar01
davidsalazar01
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 374
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



John Rowan
John Rowan
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21480 Visits: 4600
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search