HomeRecommended LinksLatest LinksTop HitsWhere's that GP Window?Contact
Home » Listing Details
Top Websites
  1. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  2. Dynamics GP Help
    Over 1700 resources listed.
  3. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 900 resources listed.
  4. Mariano Gomez at The Dynamics GP Blogster
    Over 700 resources listed.
  5. Microsoft Dynamics Partner Community Blog
    Over 700 resources listed.
  6. Mohammad Daoud's Dynamics GP Blog
    Over 500 resources listed.
  7. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 400 resources listed.
  8. Inside Microsoft Dynamics GP Official Blog
    Over 400 resources listed.
  9. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 300 resources listed.
  10. eOne Business Solutions Blog
    Over 300 resources listed.
  11. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Dynamics CPM
    Over 200 resources listed.
  14. Rose Business Solutions Blog New
    Over 200 resources listed.
  15. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  16. Victoria Yudin's Dynamics GP Website
    Over 100 resources listed.
    Victoria Yudin
  17. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  18. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  19. Leslie Vail at Dynamics Confessor Blogspot
    Over 100 resources listed.
  20. BKD Dynamics GP Insights Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.
Useful Dynamics GP Links
  1. Dynamics GP Add-On Products and ISV Solutions
  2. Dynamics GP Community Homepage - Blogs, Product Forums and Newsgroups

ID:22662
Title:Extracting data from Microsoft Dynamics GP company databases using SQL Server FOR XML and XMLNAMESPACES
URL:http://dynamicsgpblogster.blogspot.com/2012/06/extracting-data-from-microsoft-dynamics.html
Description:I truly love what I do. Really!

My job takes me just about everywhere around this great country of ours and beyond its borders in the quest of helping clients and partners get the best out of their Microsoft Dynamics GP application and data.

In reference to the latter - data - I had been asked recently by a client how they could produce XML formatted data from their Microsoft Dynamics GP databases to be consumed by some web services applications they had developed. Some conditions around this request:
  • The client did not want to implement eConnect Requester, though I have to admit this would have been a slam dunk with MSMQ queues. 
  • The XML documents needed to be rather available and changeable very quickly to serve other needs.
  • No additional investments in third party products, middlewares or the likes could be suggested since budget was pretty tight.
In other words, no eConnect, no third party products, and lots of flexibility...

The answer could only be one: use the powerful XML capabilities of T-SQL to get data out in the format required by the client.

Some theory

There's a powerful option when querying data from SQL Server for use with third party applications and/or web services. You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions.

The FOR XML clause has some great benefits:

  • It allows a SQL Server developer of Microsoft Dynamics GP consultant to write critical pieces of integration architecture without having to learn the destination system's schema.
  • Additional table columns - pieces of data, if you will - can be added to the results with relative ease.
  • It's an efficient way to process data and reduces the number of components that must be developed.
  • It can be formatted to match target schemas in order to simplify mapping and/or middleware configuration.
There are a number of options related to using the FOR XML clause in SQL Server. The most appropriate way I have found - best practice, if you will - is to declare your own namespace using the WITH XMLNAMESPACES clause and to format the XML specifically as expected with the PATH mode.

Keep in mind that SQL Server has an AUTO mode which allows it to automatically format the XML document for you, relinquishing some control from you the developer or consultant.

For more information on SQL Server FOR XML and the WITH XMLNAMESPACES clauses, please take a look at SQL Server Books Online:

MSDN - FOR XML clause - http://msdn.microsoft.com/en-us/library/ms178107.aspx
MSDN - WITH XMLNAMESPACES clause - http://msdn.microsoft.com/en-us/library/ms177400.aspx

Now a practical application...

This is a simple example on how to implement all of it together. Let's take the case of a Customer with multiple addresses. The following query should produce XML data with our customer master (RM00101) and address master information (RM00102).

CustomerExtract.sql
-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

WITH XMLNAMESPACES('http://sql.customer.extract' as "ce0")
SELECT NULL
    , (  SELECT RM00101.CUSTNMBR AS [ce0:CustomerNumber]
   ,RM00101.CUSTNAME AS [ce0:CustomerName]
   ,RM00101.CHEKBKID AS [ce0:CheckbookID]
   , ( SELECT RM00102.ADRSCODE AS [ce0:AddressCode]
    , RM00102.ADDRESS1 AS [ce0:Address1]
    , RM00102.ADDRESS2 AS [ce0:Address2]
    , RM00102.CITY AS [ce0:City]
    , RM00102.[STATE] AS [ce0:State]
    , RM00102.ZIP AS [ce0:Zipcode]
    FROM RM00102 
    WHERE RM00102.CUSTNMBR = RM00101.CUSTNMBR
    FOR XML PATH('ce0:Addresses'), TYPE)
   FROM RM00101
  FOR XML PATH('ce0:Customer'), TYPE)
 FOR XML PATH ('ce0:CustomerExtract'), TYPE

The results are pretty straight forward:

FOR XML output (formatted for display purposes only)

Nothing but XML greatness!

The above query could have been encapsulated in a stored procedure with a parameter for customer number, which could have driven the results displayed. As you can tell, getting the data you need for any destination, will depend on you specific requirements, but it's doable with the power of SQL Server and T-SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Category:
Link Owner:
Date Added:June 26, 2012 11:59:06 PM
Number Hits:0
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
Latest links
GPWindow.com

Developed and presented by
Smith & Allen Consulting, Inc.,
GP specialists since 1991.

www.saci.com
PHP Link Directory