Results 1 to 3 of 3

Thread: Parsing XML

  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Parsing XML

    If have an XML column that looks like...

    <fd eml="jdoe@aol.com" u="8b77eef6272855af" b="0" v="133">
    <field i="1">2014-09-24</field><field i="3">0</field>
    <field i="377"><v>A1</v></field>
    <field i="384">Public Property</field>
    <field i="395"><v>School</v></field>
    <field i="396"><v>All Grades</v></field>
    <field i="402"><v>Full Coverage</v></field>
    <field i="404"><v>Yes</v></field>
    <field i="403"><v>Full Sprinkler Coverage</v></field>
    <field i="221">TEST</field>
    <field i="222">1234 NO WHERE</field>
    <field i="223">PHOENIX</field>
    <field i="224"><v>Mohave</v></field>
    <field i="225"/>
    <field i="269"/>
    <field i="31">Arizona State Fire Marshal's Office 1110 West Washington St. Suite 100 Phoenix, Arizona 85007 (O) 602.364.1003</field>
    <field i="46"><v>John Doe: 20</v></field>
    <field i="148"/><field i="32">Yes</field>
    <field i="261">0</field><field i="398">1999</field>
    <field i="375"><v>FL</v></field>
    <field i="37">
    <v>Scheduled</v>
    <v>Licensing</v>
    <v>Complaint</v>
    <v>DHS / DES </v>
    <v>Re-Inspection</v></field>
    <field i="154">Hood System Final</field>

    <field i="353"><rf><cp i="0">
    <field i="177"><v>At time of final inspection this system is intalled IAW approved plans, NFPA standards and 2003 IFC. This system is approved as installed.</v></field>
    <field i="178"><v>Required Fire extinguisher not present</v></field>
    <field i="275"/>
    <field i="179">THIS IS A TEST</field>
    </cp></rf></field>

    <field i="381">Pass</field>
    <field i="379">1</field>
    <field i="378">1</field>
    <field i="391">23</field>
    <field i="316"/><field i="260"/>
    <field i="43">The items noted above, unless otherwise stated, are in compliance with the Arizona State Fire Code, A.A.C. R4-34-1101 adopted pursuant to A.R.S. 41-2146. This inspection is for your safety and the safety of the citizens of Arizona. Your cooperation is appreciated. </field>
    <field i="44"/>
    <field i="376"><v>This Permit is Closed</v></field>
    <field i="49"><v>John.Doe@aol.com</v></field>
    <field i="45">2014-09-24</field></fd>



    Then I am using the following to Parse Field 37...


    Select
    (./fd/field[@i="37"]/v)[1]','varchar(100)') AS InspectionType


    The problem is because this field is an array it only returns the last element, "Re-Inspection". I need to break the elements down. Is there a way to do this in SQL?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You will want to use the nodes function of the XML data type. You may be using the query, or even value function, now.

  3. #3
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    I figured it out. One of those smack your head ones. All I had to do was increment the node in the parse.

    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[1] ', 'varchar(100) ') AS InspectionType1,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[2] ', 'varchar(100) ') AS InspectionType2,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[3] ', 'varchar(100) ') AS InspectionType3,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[4] ', 'varchar(100) ') AS InspectionType4,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[5] ', 'varchar(100) ') AS InspectionType5,



    Full SQL...



    Use CometTracker

    Select
    Users.name as Inspector,
    phonedata.UserID,
    phonedata.RecID as PhoneRecID,
    phonedata.FormData.value( '(./fd/field[@i="1"])[1] ', 'varchar(100) ') AS FormDate,
    phonedata.FormData.value( '(./fd/field[@i="3"])[1] ', 'varchar(100) ') AS FID,
    phonedata.FormData.value( '(./fd/field[@i="376"]/v)[1] ', 'varchar(100) ') AS Status,
    phonedata.FormData.value( '(./fd/field[@i="86"])[1] ', 'varchar(100) ') AS POC,
    phonedata.FormData.value( '(./fd/field[@i="29"]/v)[1] ', 'varchar(max) ') AS FireSystem,
    phonedata.FormData.value( '(./fd/field[@i="49"]/v)[1] ', 'varchar(100) ') AS SentTo,
    phonedata.FormData.value( '(./fd/field[@i="43"])[1] ', 'varchar(max) ') AS ComplianceStatement,
    phonedata.FormData.value( '(./fd/field[@i="46"]/v)[1] ', 'varchar(100) ') AS InspectedBy,
    phonedata.FormData.value( '(./fd/field[@i="47"]/v)[1] ', 'varchar(100) ') AS Phone,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[1] ', 'varchar(100) ') AS InspectionType1,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[2] ', 'varchar(100) ') AS InspectionType2,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[3] ', 'varchar(100) ') AS InspectionType3,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[4] ', 'varchar(100) ') AS InspectionType4,
    phonedata.FormData.value( '(./fd/field[@i="37"]/v)[5] ', 'varchar(100) ') AS InspectionType5,
    phonedata.FormData.value( '(./fd/field[@i="375"]/v)[1] ', 'varchar(100) ') AS PermitType,
    phonedata.FormData.value( '(./fd/field[@i="381"])[1] ', 'varchar(50) ') AS PassFail,
    phonedata.FormData.value( '(./fd/field[@i="378"])[1] ', 'varchar(10) ') AS TravelTime,
    phonedata.FormData.value( '(./fd/field[@i="379"])[1] ', 'varchar(10) ') AS InspectionTime,
    phonedata.FormData.value( '(./fd/field[@i="261"])[1] ', 'varchar(50) ') AS PermitNumber,
    phonedata.FormData.value( '(./fd/field[@i="377"])[1] ', 'varchar(10) ') AS OccupancyType,
    phonedata.FormData.value( '(./fd/field[@i="154"])[1] ', 'varchar(max) ') AS InspectionNotes1,
    phonedata.FormData.value( '(./fd/field[@i="330"])[1] ', 'varchar(max) ') AS InspectionNotes2,
    phonedata.FormData.value( '(./fd/field[@i="192"])[1] ', 'varchar(max) ') AS InspectionNotes3,
    phonedata.FormData.value( '(./fd/field[@i="193"])[1] ', 'varchar(max) ') AS InspectionNotes6,
    phonedata.FormData.value( '(./fd/field[@i="194"])[1] ', 'varchar(max) ') AS InspectionNotes4,
    phonedata.FormData.value( '(./fd/field[@i="195"])[1] ', 'varchar(max) ') AS InspectionNotes5,
    phonedata.FormData.value( '(./fd/field[@i="198"])[1] ', 'varchar(max) ') AS InspectionNotes7,
    phonedata.FormData.value( '(./fd/field[@i="199"])[1] ', 'varchar(max) ') AS InspectionNotes8,
    phonedata.FormData.value( '(./fd/field[@i="200"])[1] ', 'varchar(max) ') AS InspectionNotes9,
    phonedata.FormData.value( '(./fd/field[@i="201"])[1] ', 'varchar(max) ') AS InspectionNotes10,
    phonedata.FormData.value( '(./fd/field[@i="181"])[1] ', 'varchar(max) ') AS InspectionNotes11,
    phonedata.FormData.value( '(./fd/field[@i="182"])[1] ', 'varchar(max) ') AS InspectionNotes12,
    phonedata.FormData.value( '(./fd/field[@i="183"])[1] ', 'varchar(max) ') AS InspectionNotes13,
    phonedata.FormData.value( '(./fd/field[@i="219"])[1] ', 'varchar(max) ') AS InspectionNotes14,
    phonedata.FormData.value( '(./fd/field[@i="184"])[1] ', 'varchar(max) ') AS InspectionNotes15,
    phonedata.FormData.value( '(./fd/field[@i="185"])[1] ', 'varchar(max) ') AS InspectionNotes16,
    phonedata.FormData.value( '(./fd/field[@i="186"])[1] ', 'varchar(max) ') AS InspectionNotes17,
    phonedata.FormData.value( '(./fd/field[@i="187"])[1] ', 'varchar(max) ') AS InspectionNotes18,
    phonedata.FormData.value( '(./fd/field[@i="188"])[1] ', 'varchar(max) ') AS InspectionNotes19,
    phonedata.FormData.value( '(./fd/field[@i="220"])[1] ', 'varchar(max) ') AS InspectionNotes20,
    phonedata.FormData.value( '(./fd/field[@i="228"])[1] ', 'varchar(max) ') AS InspectionNotes21,
    phonedata.FormData.value( '(./fd/field[@i="229"])[1] ', 'varchar(max) ') AS InspectionNotes22,
    phonedata.FormData.value( '(./fd/field[@i="230"])[1] ', 'varchar(max) ') AS InspectionNotes23,
    phonedata.FormData.value( '(./fd/field[@i="231"])[1] ', 'varchar(max) ') AS InspectionNotes24,
    phonedata.FormData.value( '(./fd/field[@i="232"])[1] ', 'varchar(max) ') AS InspectionNotes25,
    phonedata.FormData.value( '(./fd/field[@i="233"])[1] ', 'varchar(max) ') AS InspectionNotes26,
    phonedata.FormData.value( '(./fd/field[@i="259"])[1] ', 'varchar(max) ') AS InspectionNotes27,
    phonedata.FormData.value( '(./fd/field[@i="388"])[1] ', 'varchar(max) ') AS InspectionNotes28,
    phonedata.FormData.value( '(./fd/field[@i="389"])[1] ', 'varchar(max) ') AS InspectionNotes29,
    phonedata.FormData.value( '(./fd/field[@i="390"])[1] ', 'varchar(max) ') AS InspectionNotes30,
    phonedata.FormData.value( '(./fd/field[@i="189"])[1] ', 'varchar(max) ') AS InspectionNotes31,
    phonedata.FormData.value( '(./fd/field[@i="190"])[1] ', 'varchar(max) ') AS InspectionNotes32,
    phonedata.FormData.value( '(./fd/field[@i="334"])[1] ', 'varchar(max) ') AS InspectionNotes33,
    phonedata.FormData.value( '(./fd/field[@i="316"])[1] ', 'varchar(55) ') AS Reinspection,
    phonedata.FormData.value( '(./fd/field[@i="260"])[1] ', 'varchar(55) ') AS FireWatch,
    phonedata.FormData.value( '(./fd/field[@i="391"])[1] ', 'varchar(55) ') AS Miles,
    phonedata.FormData.value( '(./fd/field[@i="392"])[1] ', 'varchar(55) ') AS ViolationLevel,
    phonedata.TimeTag1 as StartTime,
    phonedata.TimeTag2 as EndTime,
    GPSData.Latitude,
    phonedata.GPSRecID1 as GPSx,
    GPSData.Longitude,
    phonedata.GPSRecID1 as GPSy,
    Tasks.Name as FormName,
    GPSData.LandMarkId

    From phonedata
    Left Outer Join Users on
    Users.UserID = phonedata.UserID
    Left Outer Join Tasks on
    Tasks.RecID = phonedata.TaskSetID
    Left Outer Join GPSData on
    GPSData.RecID = phonedata.GPSRecID1

    Where phonedata.TasksetID = 5
    and phonedata.TimeTag1 >= '09/24/2014'
    and phonedata.UserID = 359574 -- Test Tablet

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •