Parsing the following xml in sql sevrer RSS

• • Reply
  

  madan535

  Contributor

  3229 Points

  1180 Posts

  Parsing the following xml in sql sevrer

  Aug 07, 2012 04:39 PM|LINK

  How to Parsing the following xml in sql sevrer

  <rule created="8/7/2012 4:32:29 PM" assembly="1.0.0.8">
      <if>
        <field propertyName="Gender" dataType="enum" class="CodeProject.WebRule.Site.Gender" assembly="CodeProject.WebRule.Site, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
        <operator type="is" dataType="enum" />
        <value dataType="enum">1</value>
        <clause type="or" />
        <field propertyName="Name" dataType="string" maxLength="50" caseSensitive="false" />
        <operator type="contains" dataType="string" />
        <value dataType="string">b</value>
        <clause type="then" />
        <action methodName="Approve">
          <source />
          <input dataType="numeric" type="field" propertyName="Income" />
        </action>
      </if>
      <else>
        <action methodName="Approve">
          <source />
          <input dataType="numeric" type="input" min="-9007199254740992" max="9007199254740992" allowDecimal="true">0</input>
        </action>
      </else>
    </rule>

  Want to convert this into as

   IfGenderisFemaleorFull Namecontains"b"thenApprove (Annual Income)elseApprove ([0])
  

  In sql server

  Please mark the replies as answers if they help.
  • Edited by madan535 on Aug 07, 2012 04:40 PM
  • Moved by Curt_C on Aug 07, 2012 05:55 PM
• • Reply
  

  bcanonica

  Contributor

  3082 Points

  595 Posts

  Re: Parsing the following xml in sql sevrer

  Aug 07, 2012 07:15 PM|LINK

  Here is a good example of parsing xml code in sql server.  I would load the specific node you are looking for into a Bit variable then you can execute your if logic. 

  http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

  Please remember to mark the replies as answers if they help. This can be beneficial to other community members reading the thread.
  
  Thanks,
  
  BC
  
  Blog Void Impossible
• • Reply
  

  Chen Yu - MS...

  All-Star

  21829 Points

  2513 Posts

  Microsoft

  Re: Parsing the following xml in sql sevrer

  Aug 13, 2012 01:13 PM|LINK

  Hi madan535,

  From your description, I found below thread which is similar to your issue, It may help you.

  Please see: http://stackoverflow.com/questions/8204506/parsing-xml-in-sql-server-2008-to-columns

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
• • Reply
  

  Chen Yu - MS...

  All-Star

  21829 Points

  2513 Posts

  Microsoft

  Re: Parsing the following xml in sql sevrer

  Aug 14, 2012 02:55 AM|LINK

  Hi madan535,

  Please try below query, it will get the result you want. Please check:

  DECLARE @rules TABLE (ID INT, XmlRule XML)
   
  INSERT INTO @rules VALUES(1, '<rule created="8/7/2012 4:32:29 PM" assembly="1.0.0.8">
      <if>
        <field propertyName="Gender" dataType="enum" class="CodeProject.WebRule.Site.Gender" assembly="CodeProject.WebRule.Site, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
        <operator type="is" dataType="enum" />
        <value dataType="enum">1</value>
        <clause type="or" />
        <field propertyName="Name" dataType="string" maxLength="50" caseSensitive="false" />
        <operator type="contains" dataType="string" />
        <value dataType="string">b</value>
        <clause type="then" />
        <action methodName="Approve">
          <source />
          <input dataType="numeric" type="field" propertyName="Income" />
        </action>
      </if>
      <else>
        <action methodName="Approve">
          <source />
          <input dataType="numeric" type="input" min="-9007199254740992" max="9007199254740992" allowDecimal="true">0</input>
        </action>
      </else>
    </rule>')
   
  SELECT
      'If' as condition,
      T.Col.value('(field/@propertyName)[1]', 'varchar(25)') AS 'propertyName',
      T.Col.value('(operator/@type)[1]', 'varchar(25)') AS 'type',
      T.Col.value('(value/text())[1]', 'varchar(25)') AS 'Value',
      T.Col.value('(clause/@type)[1]', 'varchar(25)') AS 'type',
      T.Col.value('(field/@propertyName)[2]', 'varchar(25)') AS 'propertyName',
      T.Col.value('(operator/@type)[2]', 'varchar(25)') AS 'type',
      T.Col.value('(value/text())[2]', 'varchar(25)') AS 'Value',
      T.Col.value('(clause/@type)[2]', 'varchar(25)') AS 'type',
      T.Col.value('(action/@methodName)[1]', 'varchar(25)') AS 'methodName' ,
      T.Col.value('(action/input/@propertyName)[1]', 'varchar(25)') AS 'input' ,
      'Else' as condition,
      T2.Col2.value('(action/@methodName)[1]', 'varchar(25)') AS 'methodName' ,
      T2.Col2.value('(action/input/text())[1]', 'varchar(25)') AS 'input'
  FROM @rules r
  CROSS APPLY XmlRule.nodes('/rule/if') AS T(Col)
  CROSS APPLY XmlRule.nodes('/rule/else') AS T2(COL2)

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store
  
  
  • Marked as answer by Chen Yu - MSFT on Aug 16, 2012 02:29 AM