Link to home
Start Free TrialLog in
Avatar of bman2011
bman2011Flag for United States of America

asked on

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

Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

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
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.
SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bman2011

ASKER

Thanks guys / gals. I'll see what I can whip up and probably follow up with language I'm using for this.