bman2011
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).
Desired XML schema based on above table:
How can I write my query to show in this XML format?
My basic query right now:
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
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>
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
You can try this:
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.
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')
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys / gals. I'll see what I can whip up and probably follow up with language I'm using for this.
http://msdn.microsoft.com/en-us/library/ms178107.aspx