Click here to monitor SSC
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
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 358
Hi 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!

David



Attachments
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
@John:
Since David addressing his issues implicit to your attention ignoring other posts, I leave this thread in your hands.



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
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 358
@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

-David



John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4509
Lutz, 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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
davidsalazar01
davidsalazar01
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 358
Lutz/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!

-David



Attachments
maciej_p
maciej_p
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 17
Hey! Not sure if you still care, or if this is what you are looking for, but here it is anyway Smile
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 INT
DECLARE @exml XML
DECLARE @Root VARCHAR(64)

SET @Root = '/YourRootName'
SELECT @exml = [YourXMLColumn] FROM YourTable

EXEC sp_xml_preparedocument @idoc OUTPUT, @exml

select
columnNames.id,
columnNames.localname AS ELEMENT,
columnValue.text AS VALUE
FROM OPENXML (@idoc, @Root) columnNames
cross apply (select text FROM OPENXML (@idoc, @Root)
         where nodetype = 3 AND parentid = columnNames.id) columnValue
where columnNames.nodetype <> 3 and columnNames.localname not in ('xsi','xsd')

Exec sp_xml_removedocument @idoc
davidsalazar01
davidsalazar01
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 358
Thanks 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 :-D



Attachments
Parse_EPOSFlightDetail.txt (14 views, 8.00 KB)
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