insert data into table from XML File

Question

insert data into table from XML File

ritesh.grover.mca

SSC Enthusiast

Points: 112
More actions
August 6, 2012 at 12:50 am

#262419

Can any body help me in inserting data into a Table from this XML
<?xml version="1.0" encoding="UTF-8"?>
-<LabReport> -<ReportHeader> <Requested-By/> <Requested-On>08/05/2012 11:13 PM</Requested-On> </ReportHeader> -<Clinic Status="Active" Ext-ID="31750" AccountNumber="31750"> <Name>Zoasis Animal Clinic</Name> -<Address> <Street>100 Main Street</Street> <Street/> <City>Anytown </City> <StateProv>CA</StateProv> <PostalCode>90025</PostalCode> <Country>USA</Country> </Address> <Phone>000-000-0000</Phone> <Fax>888-555-1414</Fax> <Rtf-Version>2</Rtf-Version> </Clinic> -<LabLocation LabLocationId="7" LabId="1"> <LocationCode>IR</LocationCode> <LocationName>ANTECH Diagnostics</LocationName> -<Address> <Street>17672-A Cowan Avenue</Street> <City>Irvine</City> <StateProv>CA</StateProv> <PostalCode>92614</PostalCode> <Country>USA</Country> </Address> <LocationPhone>800-745-4725</LocationPhone> </LabLocation> -<Accession Order-Status="F" Location-ID="7" LabClinicExt-ID="987654321" Lab-ID="1" Acc-Result-ID="13626566"> -<AccessionHeader> <Accession-ID Type="Clinic-AccID" ID="31750"/> <Accession-ID Type="Chart-ID" ID="32428"/> <Accession-ID Type="Lab-AccID" ID="DEMO13626566"/> <TimeStamp Type="Order Received DateTime" Value="08/05/2012"/> <TimeStamp Type="Latest Results Received DateTime" Value="09/09/2003 01:02 PM"/> <TimeStamp Type="Printed DateTime" Value="08/05/2012 10:55 PM"/> -<Pet> <Name>CAMILLE</Name> <Age>10Y</Age> <Sex>SF</Sex> <Species Ext-ID="C">Canine</Species> <Breed Ext-ID="GERMS">German Shepherd</Breed> <Owner>MAHAFFY ERIN</Owner> <Doctor>LYONS</Doctor> </Pet> </AccessionHeader> -<UnitCode Status="F" Ext-ID="527" Type="SUPERCHEM" Profile-ID="526" OrderCode="SA020" Order-Control-Status="RE"> <Name>Superchem</Name> <TimeStamp Type="Released Datetime" Value="09/09/2003 01:02 PM"/> <TimeStamp Type="Viewed Datetime" Value="08/05/2012 10:55 PM"/> -<TestCode Status="F" Ext-ID="1000"> <Name>AST (SGOT)</Name> <Value>34</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>15-66</Range> </TestCode> -<TestCode Status="F" Ext-ID="1002" Abnormal="H"> <Name>ALT (SGPT)</Name> <Value>139</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>12-118</Range> </TestCode> -<TestCode Status="F" Ext-ID="1004"> <Name>Total Bilirubin</Name> <Value>0.1</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>0.1-0.3</Range> </TestCode> -<TestCode Status="F" Ext-ID="1010"> <Name>Alk Phosphatase</Name> <Value>63</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>5-131</Range> </TestCode> -<TestCode Status="F" Ext-ID="1012"> <Name>GGT</Name> <Value>10</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>1-12</Range> </TestCode> -<TestCode Status="F" Ext-ID="1014"> <Name>Total Protein</Name> <Value>6.9</Value> -<Units>
<![CDATA[g/dL]]>
</Units> <Range>5.0-7.4</Range> </TestCode> -<TestCode Status="F" Ext-ID="1016"> <Name>Albumin</Name> <Value>3.7</Value> -<Units>
<![CDATA[g/dL]]>
</Units> <Range>2.7-4.4</Range> </TestCode> -<TestCode Status="F" Ext-ID="1018"> <Name>Globulin</Name> <Value>3.2</Value> -<Units>
<![CDATA[g/dL]]>
</Units> <Range>1.6-3.6</Range> </TestCode> -<TestCode Status="F" Ext-ID="1020"> <Name>A/G Ratio</Name> <Value>1.2</Value> <Units/> <Range>0.8-2.0</Range> </TestCode> -<TestCode Status="F" Ext-ID="1022"> <Name>Cholesterol</Name> <Value>226</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>92-324</Range> </TestCode> -<TestCode Status="F" Ext-ID="1024"> <Name>BUN</Name> <Value>11</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>6-25</Range> </TestCode> -<TestCode Status="F" Ext-ID="1026"> <Name>Creatinine</Name> <Value>0.7</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>0.5-1.6</Range> </TestCode> -<TestCode Status="F" Ext-ID="1028"> <Name>BUN/Creatinine Ratio</Name> <Value>16</Value> <Units/> <Range>4-27</Range> </TestCode> -<TestCode Status="F" Ext-ID="1030"> <Name>Phosphorus</Name> <Value>4.1</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>2.5-6.0</Range> </TestCode> -<TestCode Status="F" Ext-ID="1032"> <Name>Calcium</Name> <Value>9.6</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>8.9-11.4</Range> </TestCode> -<TestCode Status="F" Ext-ID="1040"> <Name>Glucose</Name> <Value>72</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>70-138</Range> </TestCode> -<TestCode Status="F" Ext-ID="1042"> <Name>Amylase</Name> <Value>490</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>290-1125</Range> </TestCode> -<TestCode Status="F" Ext-ID="1044"> <Name>Lipase</Name> <Value>267</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>77-695</Range> </TestCode> -<TestCode Status="F" Ext-ID="1046"> <Name>Sodium</Name> <Value>150</Value> -<Units>
<![CDATA[mEq/L]]>
</Units> <Range>139-154</Range> </TestCode> -<TestCode Status="F" Ext-ID="1048"> <Name>Potassium</Name> <Value>4.5</Value> -<Units>
<![CDATA[mEq/L]]>
</Units> <Range>3.6-5.5</Range> </TestCode> -<TestCode Status="F" Ext-ID="1050"> <Name>Na/K Ratio</Name> <Value>33</Value> <Units/> <Range>27-38</Range> </TestCode> -<TestCode Status="F" Ext-ID="1052"> <Name>Chloride</Name> <Value>113</Value> -<Units>
<![CDATA[mEq/L]]>
</Units> <Range>102-120</Range> </TestCode> -<TestCode Status="F" Ext-ID="1056"> <Name>CPK</Name> <Value>182</Value> -<Units>
<![CDATA[IU/L]]>
</Units> <Range>59-895</Range> </TestCode> -<TestCode Status="F" Ext-ID="1310"> <Name>Triglyceride</Name> <Value>149</Value> -<Units>
<![CDATA[mg/dL]]>
</Units> <Range>29-291</Range> </TestCode> -<TestCode Status="F" Ext-ID="1081"> <Name>Osmolality, Calculated</Name> <Value>308</Value> -<Units>
<![CDATA[mOSm/kg]]>
</Units> <Range>277-311</Range> </TestCode> -<TestCode Status="F" Ext-ID="1078"> <Name>Magnesium</Name> <Value>1.9</Value> -<Units>
<![CDATA[mEq/L]]>
</Units> <Range>1.5-2.5</Range> </TestCode> </UnitCode> -<UnitCode Status="F" Ext-ID="5174" Type="CBC" Profile-ID="526" OrderCode="SA020" Order-Control-Status="RE"> <Name>CBC</Name> <TimeStamp Type="Released Datetime" Value="09/09/2003 07:23 AM"/> <TimeStamp Type="Viewed Datetime" Value="08/05/2012 10:55 PM"/> -<TestCode Status="F" Ext-ID="2000"> <Name>WBC</Name> <Value>14.1</Value> -<Units>
<![CDATA[103/mL]]>
</Units> <Range>4.0-15.5</Range> </TestCode> -<TestCode Status="F" Ext-ID="2002"> <Name>RBC</Name> <Value>7.4</Value> -<Units>
<![CDATA[106/mL]]>
</Units> <Range>4.8-9.3</Range> </TestCode> -<TestCode Status="F" Ext-ID="2004"> <Name>HGB</Name> <Value>16.1</Value> -<Units>
<![CDATA[g/dL]]>
</Units> <Range>12.1-20.3</Range> </TestCode> -<TestCode Status="F" Ext-ID="2006"> <Name>HCT</Name> <Value>43</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>36-60</Range> </TestCode> -<TestCode Status="F" Ext-ID="2008"> <Name>MCV</Name> <Value>59</Value> -<Units>
<![CDATA[fL]]>
</Units> <Range>58-79</Range> </TestCode> -<TestCode Status="F" Ext-ID="2010"> <Name>MCH</Name> <Value>21.9</Value> -<Units>
<![CDATA[pg]]>
</Units> <Range>19-28</Range> </TestCode> -<TestCode Status="F" Ext-ID="2012"> <Name>MCHC</Name> <Value>37</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>30-38</Range> </TestCode> -<TestCode Status="F" Ext-ID="2350"> <Name>Comment</Name> <Value/> <Units/> <Range/> -<Comment>
<![CDATA[RBC MORPHOLOGY NORMAL]]>
</Comment> </TestCode> -<TestCode Status="F" Ext-ID="2058" Type="CBCDIF" Related-Ext-ID="2059"> <Name>Neutrophils</Name> <Value>70</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>60-77</Range> </TestCode> -<TestCode Status="F" Ext-ID="2059" Type="CBCABS"> <Name>Absolute Neutrophils</Name> <Value>9870</Value> -<Units>
<![CDATA[/mL]]>
</Units> <Range>2060-10600</Range> </TestCode> -<TestCode Status="F" Ext-ID="2062" Type="CBCDIF" Related-Ext-ID="2063"> <Name>Lymphocytes</Name> <Value>20</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>12-30</Range> </TestCode> -<TestCode Status="F" Ext-ID="2063" Type="CBCABS"> <Name>Absolute Lymphocytes</Name> <Value>2820</Value> -<Units>
<![CDATA[/mL]]>
</Units> <Range>690-4500</Range> </TestCode> -<TestCode Status="F" Ext-ID="2066" Type="CBCDIF" Related-Ext-ID="2067"> <Name>Monocytes</Name> <Value>4</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>3-10</Range> </TestCode> -<TestCode Status="F" Ext-ID="2067" Type="CBCABS"> <Name>Absolute Monocytes</Name> <Value>564</Value> -<Units>
<![CDATA[/mL]]>
</Units> <Range>0-840</Range> </TestCode> -<TestCode Status="F" Ext-ID="2070" Type="CBCDIF" Related-Ext-ID="2071"> <Name>Eosinophils</Name> <Value>6</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>2-10</Range> </TestCode> -<TestCode Status="F" Ext-ID="2071" Type="CBCABS"> <Name>Absolute Eosinophils</Name> <Value>846</Value> -<Units>
<![CDATA[/mL]]>
</Units> <Range>0-1200</Range> </TestCode> -<TestCode Status="F" Ext-ID="2074" Type="CBCDIF" Related-Ext-ID="2075"> <Name>Basophils</Name> <Value>0</Value> -<Units>
<![CDATA[%]]>
</Units> <Range>0-1</Range> </TestCode> -<TestCode Status="F" Ext-ID="2075" Type="CBCABS"> <Name>Absolute Basophils</Name> <Value>0</Value> -<Units>
<![CDATA[/mL]]>
</Units> <Range>0-150</Range> </TestCode> -<TestCode Status="F" Ext-ID="2092"> <Name>Platelet Estimate</Name> <Value>Adequate</Value> <Units/> <Range/> </TestCode> -<TestCode Status="F" Ext-ID="9803"> <Name>Platelet Count</Name> <Value>226</Value> -<Units>
<![CDATA[103/mL]]>
</Units> <Range>170-400</Range> </TestCode> </UnitCode> -<UnitCode Status="F" Ext-ID="9818" OrderCode="ADD70" Order-Control-Status="RE"> <Name>Add-On Heartworm Antigen </Name> <TimeStamp Type="Released Datetime" Value="09/09/2003 03:59 AM"/> <TimeStamp Type="Viewed Datetime" Value="08/05/2012 10:55 PM"/> -<TestCode Status="F" Ext-ID="4010"> <Name>Heartworm Antigen</Name> <Value>Negative</Value> <Units/> <Range>Negative</Range> </TestCode> </UnitCode> </Accession> </LabReport>
August 6, 2012 at 7:11 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ritesh.grover.mca SSC Enthusiast Points: 112 More actions · Answer 1

[font="Arial Black"]i want to write a select query on this XML file stored in my table . can anybody help me how can i write this as i am new to XML[/font]

<?xml version="1.0" encoding="UTF-8"?>

-<LabReport> -<ReportHeader> <Requested-By/> <Requested-On>08/06/2012 02:56 AM</Requested-On> </ReportHeader> -<Clinic Status="Active" Ext-ID="31750" AccountNumber="31750"> <Name>Zoasis Animal Clinic</Name> -<Address> <Street>100 Main Street</Street> <Street/> <City>Anytown </City> <StateProv>CA</StateProv> <PostalCode>90025</PostalCode> <Country>USA</Country> </Address> <Phone>000-000-0000</Phone> <Fax>888-555-1414</Fax> <Rtf-Version>2</Rtf-Version> </Clinic> -<LabLocation LabLocationId="7" LabId="1"> <LocationCode>IR</LocationCode> <LocationName>ANTECH Diagnostics</LocationName> -<Address> <Street>17672-A Cowan Avenue</Street> <City>Irvine</City> <StateProv>CA</StateProv> <PostalCode>92614</PostalCode> <Country>USA</Country> </Address> <LocationPhone>800-745-4725</LocationPhone> </LabLocation> -<Accession Order-Status="F" Location-ID="7" LabClinicExt-ID="987654321" Lab-ID="1" Acc-Result-ID="13626565"> -<AccessionHeader> <Accession-ID Type="Clinic-AccID" ID="31750"/> <Accession-ID Type="Chart-ID" ID="12037"/> <Accession-ID Type="Lab-AccID" ID="DEMO13626565"/> <TimeStamp Type="Order Received DateTime" Value="08/05/2012"/> <TimeStamp Type="Latest Results Received DateTime" Value="09/11/2003 10:17 AM"/> <TimeStamp Type="Printed DateTime" Value="08/05/2012 09:11 PM"/> -<Pet> <Name>BOGEY</Name> <Age>2Y</Age> <Sex>CM</Sex> <Species Ext-ID="C">Canine</Species> <Breed Ext-ID="WHEAT">Soft Coated Wheaten Terrier</Breed> <Owner>STABLER CHERYL-RANDY</Owner> <Doctor>BRUYETTE</Doctor> </Pet> </AccessionHeader> -<UnitCode Status="F" Ext-ID="16195" OrderCode="S16195" Order-Control-Status="RE"> <Name>Cobalamine (Vitamin B12 ) and Folate</Name> <TimeStamp Type="Released Datetime" Value="09/11/2003 10:17 AM"/> <TimeStamp Type="Viewed Datetime" Value="08/05/2012 09:11 PM"/> -<TestCode Status="F" Ext-ID="1500"> <Name>Cobalamin</Name> <Value>413</Value> -<Units>

<![CDATA[pg/mL]]>

</Units> <Range>249-733</Range> </TestCode> -<TestCode Status="F" Ext-ID="1501"> <Name>Folate</Name> <Value>11.2</Value> -<Units>

<![CDATA[ng/mL]]>

</Units> <Range>6.5-11.5</Range> -<Comment>

<![CDATA[Normal B12 and folate concentrations do not exclude the presence of small intestinal disease. * TEST PERFORMED AT ANTECH'S NEW YORK LABORATORY ***** Please note new normal range.*****]]>

</Comment> </TestCode> </UnitCode> </Accession>

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Please review the information in http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx, it provides a good introduction to XML and SQL Server. By following the links you'll be able to see examples of importing XML data.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

demonfox SSCertifiable Points: 6289 More actions · Answer 3

how do you want to store it ?

check out nodes method at msdn .

http://msdn.microsoft.com/en-us/library/ms188282.aspx

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: