Solved

Using SQL to Generate XML from Large Dataset 80k+ rows

Posted on 2014-09-26
7
47 Views
Last Modified: 2016-05-23
I'm trying to generate an XML file based on the following table layout which spans out to about 80-100k rows returned when I add in all PartIDs I need to generate the XML on. Problem is, I don't have SSIS access on the SQL server, so I need to copy and paste it from the results pane. Important part to the XML output is that it must be in order of the SEQ column, this order of numbers starts over from 1 based on distinct part IDs, so the XML output for each parname would have each value applied in the order of SEQ number (see xml layout below post).

MODNM | CARNM | PARTID | PARNAME | SEQ | TYPE | VALUE | FLAG
-------------------------------------------------------------------------------------------------------
M-880 | HONA | 29000153 | SETGROUP| 1 | A | 3 | P
M-880 | HONA | 29000153 | *CDMZI | 2 | A | F%+A8E=,,,0;+MS=V123,456; | P
M-880 | HONA | 29000153 | DMTZ| 3 | A |   | P
M-880 | HONA | 29000153 | CDRFCP| 4 | A | 0 | P
M-880 | HONA | 29000153 | SETGROUP| 5 | A | 15 | P
M-880 | HONA | 29000153 | #PT| 5 | 6 | NONE | P
M-880 | HONA | 29000153 | CDH6| 7 | A | 100.111.100.44 | P
M-880 | HONA | 29000153 | CDEPORT | 8 | A | 9003 | P

Open in new window



Desired XML schema based on above table:

    <Device PartID="29000153" MODNM="M-880">
      <Car Name="HONA">
        <Group GID="3">
          <Pmeter ParName="*CDMZI" Value="F%+A8E=,,,0;+MS=V123,456;" Type="A" Flag="P" />
          <Pmeter ParName="DMTZ" Value="" Type="A" Flag="P" />
          <Pmeter ParName="CDRFCP" Value="0" Type="A" Flag="P" />
        </Group>
        <Group GID="15">
          <Pmeter ParName="#PT" Value="NONE" Type="A" Flag="P" />
          <Pmeter ParName="CDH6" Value="100.111.100.44" Type="A" Flag="P" />
          <Pmeter ParName="CDEPORT" Value="9003" Type="A" Flag="P" />
        </Group>
      </Car>
      <DevFiles />
    </Device>

Open in new window


How can I write my query to show in this XML format?

My basic query right now:
 
SELECT MODNM, CARNM, PARTID, PARNAME, SEQ, TYPE, VALUE, FLAG
FROM PINFO
WHERE PARTID IN ('29000153')   <-----All of my unique IDs would go here, for now it's just one
AND MODNM = 'M-880'
AND CARNM = 'HONA'
ORDER BY SEQ ASC

Open in new window

0
Comment
Question by:bman2011
7 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40347770
Start by just adding "for xml auto" at the end. You can customize it further if auto doesn't come out right
http://msdn.microsoft.com/en-us/library/ms178107.aspx
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40347779
You can try this:
SELECT 
	MODNM, 
	CARNM, 
	PARTID, 
	PARNAME, 
	SEQ, 
	TYPE, 
	VALUE, 
	FLAG
FROM 
	PINFO
WHERE 
	PARTID IN ('29000153')   <-----All of my unique IDs would go here, for now it's just one
	AND MODNM = 'M-880'
	AND CARNM = 'HONA'
ORDER BY SEQ ASC
FOR XML PATH('CarName')

Open in new window


However this will give you every column as a separate element and not a hierarchy. I am not sure you can get a hierarchical XML directly from SQL query.
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 250 total points
ID: 40347830
You can, but it takes some manual work to get it to show up exactly how you want. Here is a good example to start with. They add some stuff and put in some sorting so it shows in the correct order
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28369869.html
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 40347893
Oh my god! I gave solution to that question and I completely forgot about it. :D However in that question there was already a hierarchical structure in the table, which doesn't exist in this case. So you will have to build one...
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 40348101
There are a couple of reasons why this is not feasible using T-SQL.  
1. While you can get pretty close using FOR XML EXPLICIT (I just did), it is extremely cumbersome for a single part, I would hate to think what it would be like with the number of parts you have in mind.  You have to use 4 UNIONd statements in order to achieve this hierarchy.

Somewhat related to this, yes, I understand that the SETGROUP starts a new Group, however T-SQL does not work this way, and your best bet would be to add the GID to all the rows with the same Group, before executing the FOR XML EXPLICIT.

2. Assuming that you have surmounted the hurdle from the previous point.  You then have the problem that this is simply not scalable using T-SQL.

Here is my suggestion:  Use .NET.  This is exactly what it was designed for.
0
 

Author Comment

by:bman2011
ID: 40356311
Thanks guys / gals. I'll see what I can whip up and probably follow up with language I'm using for this.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question