XML : SQL query of Multi Level XML

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