CodePartners - Software Development - Dallas, Texas Software Development - Sage Accpac Customization - SageCRM Customization
Login
pay invoice online  |  remote assistance  |  
  • INTERNET APPLICATION DEVELOPMENT

    • Services
      • Web Application Development
      • Mobile Applications
      • Integration Solutions
      • Staff Augmentation
    • Technologies
    • How we work
    • About us
    • Success stories
    • Blog
  • MID MARKET ERP DEVELOPMENT

    • Intacct
    • Sage CRM
    • Sage
    • Sage Component Catalog
    • How We Work
    • About Us
    • Success Stories
    • Blog

Blog

CodePartners Blog

rss


How to Query Using Microsoft SQL Server to Create XML, Part II

SHARE THIS
Facebook twitter linkedin Print Print
Print Email

by Sheila Zhang

This blog is a continuation from my previous blog: How to Query Using Microsoft SQL Server to Create XML, found here. 

Another great query using Microsoft SQL Server to create XML is using this process:

You can manually define your selected SML format by using the PATH option of the FOR XML clause.  In the PATH mode, the standard XML XPath, which describes the path to the element in the generated XML, defines the column names and aliases. The Path is this defined hierarchical:

- Levels are de-limited with the slash (/) character
- Prefix the alias name with the “at” (@) character to generate attribute-centric XML

Below is a simple SQL illustration.

This creates a single customer level of XML data:

From here, you create XML data with nested elements for the child table by using a sub-query within the PATH mode.  

This example shows the order nested under each customer:

It gives this result:

Now that you know how to create XML from relational data, I can show you how to convert XML to relational tables, which is also referred to as “Shredding XML”.  

I begin with this XML data:

Then, I use this statement to convert the XML data to a data table.

**Please note, I use the third value “11”, however you can try other options such as 1 and 2. 

Attribute-centric mapping refers to 1.  For example, BILNAME and ORDNUMBER will not populate because the XML data defines them as elements.

Element-centric mapping refers to 2.  For example, ID will not populate because the XML data defines it as attributes.

However, if attribute-centric and element-centric mapping refers to 11, then by combining flag 8 with flags 1 and 2, you see all the populated fields.

I hope you continue to learn and use my examples on how to query using Microsoft SQL server and to create XML data in your daily projects.  If you have any questions, please post them in the comments section.




Comments are closed.
On November 11, 2013 in Development [Post:by]
Tagged With: MSSQL, SQL, SQL Query, XML / 6063 Views

Search

Categories

  • General (166) rss
  • Development (158) rss
  • Success Stories (40) rss
  • Products (29) rss
  • Uncategorized (18) rss
  • BTerrell Group (5) rss
  • Project Management (22) rss
  • Web Development (57) rss
  • Web application (33) rss
  • Integration (19) rss
  • Mobile Development (7) rss

Archives

  • 2015
    • January (5)
    • February (7)
    • March (6)
    • April (5)
    • May (2)
  • 2014
    • January (13)
    • February (12)
    • March (13)
    • April (12)
    • May (11)
    • June (12)
    • July (11)
    • August (13)
    • September (10)
    • October (8)
    • November (6)
    • December (4)
  • 2013
    • April (73)
    • May (22)
    • June (20)
    • July (20)
    • August (22)
    • September (19)
    • October (13)
    • November (12)
    • December (7)

Tags

.NET Accpac app development application customization application development automation Azure C# cloud components Crystal Reports customization development ERP general ledger HTML5 Intacct Intacct Platform Services integration JQuery load balancer mass update mobile development OCR Order Entry outsourcing PO pricing product development project management REST Sage 300 ERP Sage CRM software integration trigger user interface Visual Studio web application web development website development Workforce Go! .NET ACA accpac Ajax Amazon AP invoices application development Arxis ASP.NET automation Axure BTerrell Group C# Cloud computing CodePartners Constant Contact credit card processing CRM crystal reports custom software Customer Service Customization Delete team project design phase Development doc-link DRILLDWNLK ERP event Excel GLAFS Intacct Intacct Advantage Intacct Platform Services Integration Java script JavaScript jQuery Kerr Consulting LinkedIn Microsoft MSSQL Offshoring outsourcing payroll PCI compliance PCI-DSS Pivot PJC project management sage Sage 300 Sage 300 ERP Sage Accpac ERP Sage CRM Smart Rule social media software testing SQL SQL Query SQL Server staff augmentation sub-ledger success success story TFS Trouble shooting UI design Visual Studio WCF Web API web application web design web development Webinar website Windows Windows Azure XML
Email this article from Codepartners.com
Separate multiple addresses with commas(,)
To: *   Copy me
Subject:
From: *
Message:
ABOUT US / CONTACT US / NEWS & EVENTS
CodePartners / 16200 Addison Rd. STE 270 / Addison, Texas 75001 / Phone: 214-647-2611 / Toll Free: 866-647-2611 / Fax: 214-647-2612 / info@codepartners.com
© 2006 - 2015 codepartners.com All Rights Reserved.