Question

I m trying to parse XML that is stored as a column in a table into it s basic components. The XML describes a rule, below is an example.

以下例子为:“Date = 12/23/2011 and Change = No”。

I d like to get the and operator between the rules (BOOLEAN AND) in a column, the left hand side and right hand side for each rule into columns (DATE, 12/23/2011), and the operation between LHS and RHS in another column (EQUAL TO).

    <Conditions>
  <FactsetConditionBase xmlns:d2p1="http://www.w3.org/2001/XMLSchema-instance" d2p1:type="FactsetExpression" Operation="Boolean And">
    <Conditions>
      <FactsetStatement Operation="Equal To">
        <Identifier Value="Date" />
        <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" d2p1:type="q1:string">12/23/2011</Value>
      </FactsetStatement>
      <FactsetStatement Operation="Equal To">
        <Identifier Value="Change" />
        <Value xmlns:q2="http://www.w3.org/2001/XMLSchema" d2p1:type="q2:string">No</Value>
      </FactsetStatement>
    </Conditions>
  </FactsetConditionBase>
</Conditions>

这些规则的范围也越来越复杂。

更为复杂的规则:(WeekDay = 星期一和(从1个或1个数目开始,从2个或3个开始)

<Conditions>
  <FactsetConditionBase xmlns:d2p1="http://www.w3.org/2001/XMLSchema-instance" d2p1:type="FactsetExpression" Operation="Boolean And">
    <Conditions>
      <FactsetExpression Operation="Boolean And">
        <Conditions>
          <FactsetExpression Operation="Boolean And">
            <Conditions>
              <FactsetStatement Operation="Equal To">
                <Identifier Value="WeekDay" />
                <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" d2p1:type="q1:string">Monday</Value>
              </FactsetStatement>
            </Conditions>
          </FactsetExpression>
          <FactsetExpression Operation="Boolean Or">
            <Conditions>
              <FactsetStatement Operation="Begins With">
                <Identifier Value="Number" />
                <Value xmlns:q2="http://www.w3.org/2001/XMLSchema" d2p1:type="q2:string">1</Value>
              </FactsetStatement>
              <FactsetStatement Operation="Begins With">
                <Identifier Value="Number" />
                <Value xmlns:q3="http://www.w3.org/2001/XMLSchema" d2p1:type="q3:string">2</Value>
              </FactsetStatement>
              <FactsetStatement Operation="Begins With">
                <Identifier Value="Number" />
                <Value xmlns:q4="http://www.w3.org/2001/XMLSchema" d2p1:type="q4:string">3</Value>
              </FactsetStatement>
            </Conditions>
          </FactsetExpression>
        </Conditions>
      </FactsetExpression>
    </Conditions>
  </FactsetConditionBase>
</Conditions>

较为复杂的规则:科罗=RED

<Conditions>
  <FactsetConditionBase xmlns:d2p1="http://www.w3.org/2001/XMLSchema-instance" d2p1:type="FactsetStatement" Operation="Equal To">
    <Identifier Value="Color" />
    <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" d2p1:type="q1:string">RED</Value>
  </FactsetConditionBase>
</Conditions>

事先得到任何援助。

Answer 1

如何做到这一点?

DECLARE @rules TABLE (ID INT, XmlRule XML)

INSERT INTO @rules VALUES(1,  <Conditions>
  <FactsetConditionBase xmlns:d2p1="http://www.w3.org/2001/XMLSchema-instance" d2p1:type="FactsetExpression" Operation="Boolean And">
    <Conditions>
      <FactsetStatement Operation="Equal To">
        <Identifier Value="Date" />
        <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" d2p1:type="q1:string">12/23/2011</Value>
      </FactsetStatement>
      <FactsetStatement Operation="Equal To">
        <Identifier Value="Change" />
        <Value xmlns:q2="http://www.w3.org/2001/XMLSchema" d2p1:type="q2:string">No</Value>
      </FactsetStatement>
    </Conditions>
  </FactsetConditionBase>
</Conditions> )

SELECT
    r.ID,
    T.Col.value( (@Operation)[1] ,  varchar(25) ) AS  Operation ,
    T2.Col2.value( (@Operation)[1] ,  varchar(25) ) AS  Operation2 ,
    T2.Col2.value( (Identifier/@Value)[1] ,  varchar(25) ) AS  Identifier ,
    T2.Col2.value( (Value/text())[1] ,  varchar(25) ) AS  Value 
FROM @rules r
CROSS APPLY XmlRule.nodes( /Conditions/FactsetConditionBase ) AS T(Col)
CROSS APPLY T.Col.nodes( ./Conditions/FactsetStatement ) AS T2(Col2)

它给我一个产出:

ID Operation    Operation2  Identifier   Value
1  Boolean And  Equal To      Date       12/23/2011
1  Boolean And  Equal To      Change     No

你们是否回想?

友情链接