Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Working with XML Fields

    This is my first time working with XML fields.
    I am trying a simple:

    Select XMLField.Value('(./field)[1]','varchar(100)') as Data

    but I get nothing back. I do not know how to handle the 'i="1"' stuff.

    Can you help me out?


    This is what the XML looks like:

    <fd u="5e455ef114194473" b="1" v="31">
    <field i="1">2012-07-12</field>
    <field i="147">
    <v>Final Inspection</v>
    </field>
    <field i="3">
    <v>Add Facility</v>
    </field>
    <field i="81">2031</field>
    <field i="82">PUBLIC SERVICE FACILITY</field>
    <field i="83">222 E. JAVELINA</field>
    <field i="84">MESA</field>
    <field i="85">MARICOPA</field>
    <field i="86" />
    <field i="87" />
    <field i="29" />
    <field i="30" />
    <field i="46">
    <v>Brad Bulgrin Deputy: 69</v>
    </field>
    <field i="148" />
    <field i="47">
    <v>(602) 850-1531</v>
    </field>
    <field i="32">12-1092</field>
    <field i="51">
    <v>FA</v>
    </field>
    <field i="33">1</field>
    <field i="34">. 5</field>
    <field i="35">Pass</field>
    <field i="36">
    <v>I3</v>
    </field>
    <field i="37">
    <v>Construction</v>
    </field>
    <field i="38">
    <rf>
    <cp i="0">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">THIS WAS A SUCCESSFUL FIRE ALARM TENANT IMPROVEMENT.</field>
    </cp>
    <cp i="1">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">THE SYSTEM WAS INSTALLED PER APPROVED PLANS.</field>
    </cp>
    <cp i="2">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">PERMIT APPROVED</field>
    </cp>
    </rf>
    </field>
    <field i="44">[Signature]</field>
    <field i="45" />
    </fd>

  2. #2
    Join Date
    Aug 2012
    Posts
    30

    getting XML elements

    see illustration below
    Code:
    declare @x xml
    
    set @x='<fd u="5e455ef114194473" b="1" v="31">
    <field i="1">2012-07-12</field>
    <field i="147">
    <v>Final Inspection</v>
    </field>
    <field i="3">
    <v>Add Facility</v>
    </field>
    <field i="81">2031</field>
    <field i="82">PUBLIC SERVICE FACILITY</field>
    <field i="83">222 E. JAVELINA</field>
    <field i="84">MESA</field>
    <field i="85">MARICOPA</field>
    <field i="86" />
    <field i="87" />
    <field i="29" />
    <field i="30" />
    <field i="46">
    <v>Brad Bulgrin Deputy: 69</v>
    </field>
    <field i="148" />
    <field i="47">
    <v>(602) 850-1531</v>
    </field>
    <field i="32">12-1092</field>
    <field i="51">
    <v>FA</v>
    </field>
    <field i="33">1</field>
    <field i="34">. 5</field>
    <field i="35">Pass</field>
    <field i="36">
    <v>I3</v>
    </field>
    <field i="37">
    <v>Construction</v>
    </field>
    <field i="38">
    <rf>
    <cp i="0">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">THIS WAS A SUCCESSFUL FIRE ALARM TENANT IMPROVEMENT.</field>
    </cp>
    <cp i="1">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">THE SYSTEM WAS INSTALLED PER APPROVED PLANS.</field>
    </cp>
    <cp i="2">
    <field i="39" />
    <field i="50" />
    <field i="52" />
    <field i="53" />
    <field i="54" />
    <field i="40" />
    <field i="41" />
    <field i="42" />
    <field i="48">PERMIT APPROVED</field>
    </cp>
    </rf>
    </field>
    <field i="44">[Signature]</field>
    <field i="45" />
    </fd>'
    
    select t.u.value('./field[@i="1"][1]','datetime') AS datevalueof1,
     t.u.value('(./field[@i="147"]/v)[1]','varchar(100)') AS valueof147,
     t.u.value('(./field[@i="44"])[1]','varchar(100)') AS valueof44,
     t.u.value('(./field[@i="37"]/v)[1]','varchar(100)') AS valueof37
    from @x.nodes('/fd')t(u)
    
    output
    --------------------------------------------------
    datevalueof1	              valueof147	valueof44	                                  valueof37
    2012-07-12 00:00:00.000	Final Inspection	[Signature]	                                  Construction

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

    Smile Thanks

    I had something like...

    Select
    XMLField.value('(./fd/field[@i="1"])[1]','varchar(100)') AS datevalueof1,
    XMLField.value('(./fd/field[@i="147"]/v)[1]','varchar(100)') AS valueof147,
    XMLField.value('(./fd/field[@i="44"])[1]','varchar(100)') AS valueof44,
    XMLField.value('(./fd/field[@i="37"]/v)[1]','varchar(100)') AS valueof37

    I could not use datetime because the data did not have a complete datetime. I will convert it on the back end.

    Thanks again.

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    Welcome
    Glad that I could be of help

Posting Permissions

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