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;
-- Itzik Ben-Gan 2001