Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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

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
bman2011
Asked:
bman2011
2 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
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
 
ZberteocCommented:
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
 
Aaron TomoskyTechnology ConsultantCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ZberteocCommented:
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
 
Anthony PerkinsCommented:
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
 
bman2011Author Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now