August 13, 2008 at 3:18 pm
Hello, folks. I have written the following SQL statement:
SELECT
c.idChemical
,s.chemicalSubLeft AS SpecDescription
,s.chemicalSubRight AS SpecValue
,p.measurement AS Measurement
,p.price AS Price
FROM
chemical AS c
INNER JOIN chemicalSubInfo AS s ON (c.idChemical = s.idChemical)
INNER JOIN chemicalPrice AS p ON (c.idChemical = p.idChemical)
ORDER BY c.idChemical
FOR XML AUTO
... and it's close to what I need. it's returning me XML that looks like this:
<c idChemical="1">
<s SpecDescription="Assay (GC)" SpecValue="99%">
<p Measurement="500 g" Price="97.3400" />
</s>
<s SpecDescription="Melting Point" SpecValue="112-115°C">
<p Measurement="500 g" Price="97.3400" />
</s>
</c>
<c idChemical="2">
<s SpecDescription="Assay (potentiometric)" SpecValue="min. 99%">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Biological Buffer pKa at 20°C" SpecValue="6.9">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Chloride" SpecValue="0.05%">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Copper, Iron, Lead" SpecValue="5 ppm">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Loss on drying at 110°C" SpecValue="0.2%">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Sodium" SpecValue="0.01%">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Sulfate" SpecValue="0.005%">
<p Measurement="250 g" Price="198.9800" />
</s>
<s SpecDescription="Sulfated Ash" SpecValue="0.2%">
<p Measurement="250 g" Price="198.9800" />
</s>
</c>
<c idChemical="3">
<s SpecDescription="Assay (GC)" SpecValue="min. 99%">
<p Measurement="500 mL" Price="72.5200" />
</s>
<s SpecDescription="Density 20°/4°" SpecValue="0.779-0.782">
<p Measurement="500 mL" Price="72.5200" />
</s>
</c>
<c idChemical="4">
<s SpecDescription="Assay (GC)" SpecValue="min. 99%">
<p Measurement="1 kg" Price="119.9700" />
</s>
<s SpecDescription="Assay (GC)" SpecValue="min. 99%">
<p Measurement="50 kg" Price="0.0000" />
</s>
<s SpecDescription="Melting range" SpecValue="78-81°C">
<p Measurement="1 kg" Price="119.9700" />
</s>
<s SpecDescription="Melting range" SpecValue="78-81°C">
<p Measurement="50 kg" Price="0.0000" />
</s>
</c>
What I really need is something a little more simplified:
<c idChemical="1">
<s SpecDescription="Assay (GC)" SpecValue="99%" />
<s SpecDescription="Melting Point" SpecValue="112-115°C" />
<p Measurement="500 g" Price="97.3400" />
</c>
<c idChemical="2">
<s SpecDescription="Assay (potentiometric)" SpecValue="min. 99%" />
<s SpecDescription="Biological Buffer pKa at 20°C" SpecValue="6.9" />
<s SpecDescription="Chloride" SpecValue="0.05%" />
<s SpecDescription="Copper, Iron, Lead" SpecValue="5 ppm" />
<s SpecDescription="Loss on drying at 110°C" SpecValue="0.2%" />
<s SpecDescription="Sodium" SpecValue="0.01%" />
<s SpecDescription="Sulfate" SpecValue="0.005%" />
<s SpecDescription="Sulfated Ash" SpecValue="0.2%" />
<p Measurement="250 g" Price="198.9800" />
</c>
<c idChemical="3">
<s SpecDescription="Assay (GC)" SpecValue="min. 99%" />
<s SpecDescription="Density 20°/4°" SpecValue="0.779-0.782" />
<p Measurement="500 mL" Price="72.5200" />
</c>
<c idChemical="4">
<s SpecDescription="Assay (GC)" SpecValue="min. 99%" />
<s SpecDescription="Melting range" SpecValue="78-81°C" />
<p Measurement="1 kg" Price="119.9700" />
<p Measurement="50 kg" Price="0.0000" />
</c>
... any suggestions? Also, I hope I'm not breaking anything XML wise by looking for the result I'm looking for. If I am, please let me know of this as well.
August 14, 2008 at 8:27 am
You can set For XML as Path or Explicit instead of Auto. Path might do what you need, I'm not sure. Explicit allows you to define how you want it broken down, and will definitely do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 12:51 pm
Using FOR XML PATH, this should be close.
SELECT c.idChemical as "c/@idChemical",
(SELECT s.chemicalSubLeft as "s/@SpecDescription" FROM chemicalSubInfo s WHERE c.idChemical = s.idChemical FOR XML PATH (''), TYPE),
(SELECT s.chemicalSubLeft as "p/@measurement" FROM chemicalSubInfo s WHERE c.idChemical = s.idChemical FOR XML PATH (''), TYPE),
FROM chemical c
FOR XML PATH ('c')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply