CODEWITHSUNDEEP
xmlsql-server-2008
macwiz
macwiz

Reputation: 31

Parsing XML in SQL Server 2008 to columns

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.

The example below would read as: "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>

These rules scale to become more or less complex as well.

A more complex rule: (WeekDay = Monday and (Number begins with 1 or Number begins with 2 or Number begins with 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>

A less complex rule: Color = 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>

Thanks in advance for any assistance.

Upvotes: 2

Views: 6161

Answers (1)

marc_s
marc_s

Reputation: 754308

How about this?

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)

It gives me an output of:

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

Is that what you're looking for??

Upvotes: 2

Related Questions