?
Solved

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

Posted on 2014-09-26
7
Medium Priority
?
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 27

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 1000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 27

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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

777 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