Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to extract a column value from data stored in XML column. Expand / Collapse
Author
Message
Posted Friday, December 20, 2013 8:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 9:32 AM
Points: 44, Visits: 115
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>
Post #1525026
Posted Monday, December 23, 2013 2:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:29 AM
Points: 2,551, Visits: 1,615
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
Post #1525445
Posted Tuesday, December 24, 2013 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 9:32 AM
Points: 44, Visits: 115
Thank you very much.
Post #1525748
Posted Thursday, January 23, 2014 2:31 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 556, Visits: 2,581
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;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1534268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse