How to extract a column value from data stored in XML column.

  • I have a XML column having AIF( Application Integration Framework ) Message data. I want to extract invoice number from XML data given below. Please help me.

    XML message data is given below:

    <Envelope xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <Header>

    <MessageId>{5603D03A-4380-404D-9F27-738BE0FEA13E}</MessageId>

    <Action>http://tempuri.org/LedgerJournalService/create</Action&gt;

    </Header>

    <Body>

    <MessageParts xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <LedgerJournal xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/LedgerJournal">

    <LedgerJournalTable class="entity">

    <CurrencyCode>USD</CurrencyCode>

    <JournalName>Day1</JournalName>

    <JournalTotalCredit>50</JournalTotalCredit>

    <JournalTotalDebit>50</JournalTotalDebit>

    <JournalType>Daily</JournalType>

    <Name>Daily Journal 1</Name>

    <VoucherSeries>Ledger_3</VoucherSeries>

    <LedgerJournalTrans class="entity">

    <AccountType>Ledger</AccountType>

    <AmountCurDebit>50</AmountCurDebit>

    <CurrencyCode>USD</CurrencyCode>

    <DocumentDate>2007-05-28</DocumentDate>

    <Invoice>00003</Invoice>

    <OffsetAccount>12020</OffsetAccount>

    <OffsetAccountType>Ledger</OffsetAccountType>

    <TransDate>2007-05-28</TransDate>

    <Txt>AxLedgerJournal inbound test trans 1</Txt>

    </LedgerJournalTrans>

    </LedgerJournalTable>

    </LedgerJournal>

    </MessageParts>

    </Body>

    </Envelope>

  • You can extract specific nodes by use the xml values() method. Additionally, because the data that you have is using a namespace, you also in this instance need to declare those as part of the query and reference them accordingly.

    CREATE TABLE #t (XMLDATA XML)

    INSERT INTO #t (XMLDATA)

    VALUES ('<Envelope xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <Header>

    <MessageId>{5603D03A-4380-404D-9F27-738BE0FEA13E}</MessageId>

    <Action>http://tempuri.org/LedgerJournalService/create</Action&gt;

    </Header>

    <Body>

    <MessageParts xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <LedgerJournal xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/LedgerJournal">

    <LedgerJournalTable class="entity">

    <CurrencyCode>USD</CurrencyCode>

    <JournalName>Day1</JournalName>

    <JournalTotalCredit>50</JournalTotalCredit>

    <JournalTotalDebit>50</JournalTotalDebit>

    <JournalType>Daily</JournalType>

    <Name>Daily Journal 1</Name>

    <VoucherSeries>Ledger_3</VoucherSeries>

    <LedgerJournalTrans class="entity">

    <AccountType>Ledger</AccountType>

    <AmountCurDebit>50</AmountCurDebit>

    <CurrencyCode>USD</CurrencyCode>

    <DocumentDate>2007-05-28</DocumentDate>

    <Invoice>00003</Invoice>

    <OffsetAccount>12020</OffsetAccount>

    <OffsetAccountType>Ledger</OffsetAccountType>

    <TransDate>2007-05-28</TransDate>

    <Txt>AxLedgerJournal inbound test trans 1</Txt>

    </LedgerJournalTrans>

    </LedgerJournalTable>

    </LedgerJournal>

    </MessageParts>

    </Body>

    </Envelope>');

    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/dynamics/2008/01/ documents/Message',

    'http://schemas.microsoft.com/dynamics/2008/01/ documents/LedgerJournal' AS LJ)

    SELECT XMLDATA.value('(/Envelope/Body/MessageParts/LJ:LedgerJournal/LJ:LedgerJournalTable/LJ:LedgerJournalTrans/LJ:Invoice)[1]', 'varchar(50)') AS 'InvNumber'

    FROM #t

    DROP TABLE #t

    Looking at the data, i'm guessing you may have some repeating sections in there so you may also need to crack open the CROSS APPLY operator as well

  • Thank you very much.

  • arthurolcot (12/23/2013)


    You can extract specific nodes by use the xml values() method. Additionally, because the data that you have is using a namespace, you also in this instance need to declare those as part of the query and reference them accordingly.

    CREATE TABLE #t (XMLDATA XML)

    INSERT INTO #t (XMLDATA)

    VALUES ('<Envelope xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <Header>

    <MessageId>{5603D03A-4380-404D-9F27-738BE0FEA13E}</MessageId>

    <Action>http://tempuri.org/LedgerJournalService/create</Action&gt;

    </Header>

    <Body>

    <MessageParts xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/Message">

    <LedgerJournal xmlns = "http://schemas.microsoft.com/dynamics/2008/01/

    documents/LedgerJournal">

    <LedgerJournalTable class="entity">

    <CurrencyCode>USD</CurrencyCode>

    <JournalName>Day1</JournalName>

    <JournalTotalCredit>50</JournalTotalCredit>

    <JournalTotalDebit>50</JournalTotalDebit>

    <JournalType>Daily</JournalType>

    <Name>Daily Journal 1</Name>

    <VoucherSeries>Ledger_3</VoucherSeries>

    <LedgerJournalTrans class="entity">

    <AccountType>Ledger</AccountType>

    <AmountCurDebit>50</AmountCurDebit>

    <CurrencyCode>USD</CurrencyCode>

    <DocumentDate>2007-05-28</DocumentDate>

    <Invoice>00003</Invoice>

    <OffsetAccount>12020</OffsetAccount>

    <OffsetAccountType>Ledger</OffsetAccountType>

    <TransDate>2007-05-28</TransDate>

    <Txt>AxLedgerJournal inbound test trans 1</Txt>

    </LedgerJournalTrans>

    </LedgerJournalTable>

    </LedgerJournal>

    </MessageParts>

    </Body>

    </Envelope>');

    WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/dynamics/2008/01/ documents/Message',

    'http://schemas.microsoft.com/dynamics/2008/01/ documents/LedgerJournal' AS LJ)

    SELECT XMLDATA.value('(/Envelope/Body/MessageParts/LJ:LedgerJournal/LJ:LedgerJournalTable/LJ:LedgerJournalTrans/LJ:Invoice)[1]', 'varchar(50)') AS 'InvNumber'

    FROM #t

    DROP TABLE #t

    Looking at the data, i'm guessing you may have some repeating sections in there so you may also need to crack open the CROSS APPLY operator as well

    I know this is a little old but you can speed this up dramatically by adding a reference to a text() node at the end of your XPath expression.

    WITH XMLNAMESPACES

    (DEFAULT 'http://schemas.microsoft.com/dynamics/2008/01/ documents/Message',

    'http://schemas.microsoft.com/dynamics/2008/01/ documents/LedgerJournal' AS LJ)

    SELECT XMLDATA.value('(//LJ:Invoice/text())[1]', 'varchar(50)') AS 'InvNumber'

    FROM #t;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply