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

Question

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

Vinod Suthar

Old Hand

Points: 322
More actions
December 20, 2013 at 8:39 am

#286490

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>
</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>

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

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

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 1

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>

</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

Vinod Suthar Old Hand Points: 322 More actions · Answer 2

Vinod Suthar

Old Hand

Points: 322

December 24, 2013 at 7:21 am

#1675773

Thank you very much.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

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>
</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