CODEWITHSUNDEEP
sqlsql-server
VladimirY
VladimirY

Reputation: 1

SQL extract string from large string in column by left of keyword search

I have a table where one of the columns is XML code condensed into one line. Because it is XML, I cannot do a charIndex for there are many '<' '>' in the line.

I need a way to extract a two letter code from a string search on the column.

EX) the column is called 'XMLMessage' and contains : 

<p1:UniversalInterchange xmlns:p1="http://www.cargowise.com/Schemas/Native">
  <Header xmlns="">
    <SenderID/>
    <RecipientID/>
  </Header>
  <Body xmlns="">
    <UniversalEvent>
      <Event>
        <EventType>DEP</EventType>
        <EventTime>2016-02-04T13:37:00</EventTime>
        <ContextCollection>
          <Context>
            <Type>MAWBNumber</Type>
            <Value>057-23154670</Value>
          </Context>
          <Context>
            <Type>MAWBOriginIATAAirportCode</Type>
            <Value>IAD</Value>
          </Context>
          <Context>
            <Type>MAWBDestinationIATAAirportCode</Type>
            <Value>TUN</Value>
          </Context>
          <Context>
            <Type>MAWBNumberOfPieces</Type>
            <Value>1</Value>
          </Context>
          <Context>
            <Type>OtherServiceInfo</Type>
            <Value>
              PRD-XPS
            </Value>
          </Context>
          <Context>
            <Type>SourceEventCode</Type>
            <Value>DEP</Value>
          </Context>
          <Context>
            <Type>NumberOfPieces</Type>
            <Value>1</Value>
          </Context>
          <Context>
            <Type>WeightOfGoods</Type>
            <Value>2KG</Value>
          </Context>
          <Context>
            <Type>IATACarrierCode</Type>
            <Value>AF</Value>
          </Context>
          <Context>
            <Type>FlightNumber</Type>
            <Value>1184</Value>
          </Context>
          <Context>
            <Type>FlightDate</Type>
            <Value>04-FEB-2016</Value>
          </Context>
          <Context>
            <Type>OriginIATAAirportCode</Type>
            <Value>CDG</Value>
          </Context>
          <Context>
            <Type>DestinationIATAAirportCode</Type>
            <Value>TUN</Value>
          </Context>
          <Context>
            <Type>TimeOfDeparture</Type>
            <Value>2016-02-04T13:37:00</Value>
          </Context>
          <Context>
            <Type>TimeOfArrival</Type>
            <Value>2016-02-04T15:40:00</Value>
          </Context>
        </ContextCollection>
        <AdditionalFieldsToUpdateCollection>
          <AdditionalFieldsToUpdate>
            <Type>JobConsolTransport.JW_ATD</Type>
            <Value>2016-02-04T13:37:00</Value>
          </AdditionalFieldsToUpdate>
        </AdditionalFieldsToUpdateCollection>
      </Event>
    </UniversalEvent>
  </Body>
</p1:UniversalInterchange>

I need to find the 'AF' after the 'IATACarrierCode' search.

I need a select statement to find the 'AF' after 'IATACarrierCode' It is always a 2 character string after.

Upvotes: 0

Views: 146

Answers (3)

VladimirY
VladimirY

Reputation: 1

SELECT distinct substring(EI_BodyNText, CHARINDEX('IATACarrierCode</Type><Value>', EI_BodyNText)+29, 2) from dbo.EDIInterchange

this works with charindex and substring

Upvotes: 0

Paul Abbott
Paul Abbott

Reputation: 7211

SQL Server has native support for XML, there is no reason to pick the field contents apart as a string.

Assuming it is an XML column:

SELECT c.value('(../Value)[1]', 'varchar(30)')
FROM table
CROSS APPLY table.xmlfield.nodes('(//Context/Type[text()="IATACarrierCode"])[1]') as t(c)

Or if it's just a string:

DECLARE @val XML = '<p1:UniversalInterchange...blah blah blah'
SELECT c.value('(../Value)[1]', 'varchar(30)') FROM @val.nodes('(//Context/Type[text()="IATACarrierCode"])[1]') as t(c)

Upvotes: 0

Andrew
Andrew

Reputation: 69

If I understand your question correctly this is how I did it

        create  table ##aw 
        (
            strr varchar(max)
        )

        insert ##aw values('<p1:UniversalInterchange xmlns:p1="http://www.cargowise.com/Schemas/Native"><Header xmlns=""><SenderID/><RecipientID/></Header><Body xmlns=""><UniversalEvent><Event><EventType>DEP</EventType><EventTime>2016-02-04T13:37:00</EventTime><ContextCollection><Context><Type>MAWBNumber</Type><Value>057-23154670</Value></Context><Context><Type>MAWBOriginIATAAirportCode</Type><Value>IAD</Value></Context><Context><Type>MAWBDestinationIATAAirportCode</Type><Value>TUN</Value></Context><Context><Type>MAWBNumberOfPieces</Type><Value>1</Value></Context><Context><Type>OtherServiceInfo</Type><Value>PRD-XPS
        </Value></Context><Context><Type>SourceEventCode</Type><Value>DEP</Value></Context><Context><Type>NumberOfPieces</Type><Value>1</Value></Context><Context><Type>WeightOfGoods</Type><Value>2KG</Value></Context><Context><Type>IATACarrierCode</Type><Value>AF</Value></Context><Context><Type>FlightNumber</Type><Value>1184</Value></Context><Context><Type>FlightDate</Type><Value>04-FEB-2016</Value></Context><Context><Type>OriginIATAAirportCode</Type><Value>CDG</Value></Context><Context><Type>DestinationIATAAirportCode</Type><Value>TUN</Value></Context><Context><Type>TimeOfDeparture</Type><Value>2016-02-04T13:37:00</Value></Context><Context><Type>TimeOfArrival</Type><Value>2016-02-04T15:40:00</Value></Context></ContextCollection><AdditionalFieldsToUpdateCollection><AdditionalFieldsToUpdate><Type>JobConsolTransport.JW_ATD</Type><Value>2016-02-04T13:37:00</Value></AdditionalFieldsToUpdate></AdditionalFieldsToUpdateCollection></Event></UniversalEvent></Body></p1:UniversalInterchange>')

        select substring(strr, charindex('IATACarrierCode</Type><Value>', strr) + len('IATACarrierCode</Type><Value>'),  2) from ##aw

Upvotes: 0

Related Questions