Solved

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

Posted on 2014-09-26
7
38 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 38

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 38

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now