I have an XML file that has the following structure:
<ArrayOfCaseHistory> <CaseHistory> <Issue1> <Outcome> <TextTranslation> <OriginalValue>Application</OriginalValue> </TextTranslation> <TextTranslation> <OriginalValue>Process</OriginalValue> </TextTranslation> </Outcome> <Action> <TextTranslation> <OriginalValue>Application</OriginalValue> </TextTranslation> <TextTranslation> <OriginalValue>Process</OriginalValue> </TextTranslation> </Action> <Action> <TextTranslation> <OriginalValue>Application</OriginalValue> </TextTranslation> <TextTranslation> <OriginalValue>Process</OriginalValue> </TextTranslation> </Action> <IssueHide>Identifier</IssueHide> </Issue1> <Issue2> ........... </CaseHistory>
The query I have at this point is:
DECLARE @xml AS XML SELECT @xml = CAST(ConfigurationEntry as XML) FROM <<TableName>> SELECT A.root.value('(Issue1)[1]', 'varchar(50)') as Issue ,B.Action.value('(OriginalValue)[1]', 'varchar(50)') as ActionOriginalValue ,C.Outcome.value('(OriginalValue)[1]', 'varchar(2000)') as OriginalOutcomeValue FROM @xml.nodes('//CaseHistory') as A(root) Cross Apply A.root.nodes('Action/TextTranslation') as B(Action) Cross Apply B.Action.nodes('Outcome/TextTranslation') as C(Outcome)
What I'm trying to get from the XML is the following information,
Issue1 Action1 Outcome1 Issue1 Action2 Outcome2 (if exists) Issue1 Action3 Outcome3 (if exists) ............
Unfortunately, the result set I'm getting is
Issue1 Action1 Outcome1 Issue1 Action1 Outcome2 Issue1 Action1 Outcome3 Issue1 Action2 Outcome1 Issue1 Action2 Outcome2 Issue1 Action2 Outcome3 Issue1 Action3 Outcome1 Issue1 Action3 Outcome2 .............
I'm fairly knowledgeable in SQL, but a newbie when it comes to querying XML. So I need some help on how to pull the information I need out of the file that I have to work with. I'm not sure if using CROSS APPLY should be used here or if OUTER APPLY would be best. Or if I'm joining/referencing the tables correctly. So this is an education process for me, and any assistance would be greatly appreciated.
No comments:
Post a Comment