?
Solved

Help get SCOM values from denormalized SQL XML datatype

Posted on 2011-10-04
3
Medium Priority
?
499 Views
Last Modified: 2012-05-12
I am trying to get a simple table view to match to events in windows event logs and Service center managment packs. This does not need to be in a single select statement.


Select
  [DisplayName]
,DataSourceXML.query('/Rule/Expression/And/Expression/SimpleExpression/ValueExpression/Value') as [eventid]
From
  dbo.table1

returns all value elements at that level. I only want the integer value where xpathQueryType type='initeger'  
Please review the XML Contents of two sample rows.
Becuase of the content in Snippet ID=8224550
There should be one row with eventid 5341
The Row from the table has snippet Snippet ID=8224551
This should return 12 rows with eventid values of 4956, 4957, and 5165 through  5174.

Multiple inserts into a temp table is fine since we have a nest issue
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
Please Help


 






<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>5341</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>

Open in new window

0
Comment
Question by:Banthor
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36914321
Hi. See if this XPath will do:

'/Rule//Expression/SimpleExpression[ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value'

- Grab any node below /Rule that has an Expression/SimpleExpression node with a ValueExpression/XPathQuery element of Type="Integer" whose value is "EventDisplayNumber"
- From there grab the ValueExpression/Value element.

See attached code snippet for tested example using first two code snippets above. Was not sure if the third was just a duplicate as you only mentioned the two. I get 13 rows, which matches your question comment; therefore, I did not test third.

Kevin
;WITH dbo_table1(DisplayName, DataSourceXML) AS (
SELECT 'Event1',
CONVERT(XML, 
'<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>5341</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>'
)
UNION ALL SELECT 'Event2',
CONVERT(XML,
'<Rule>
  <ComputerName>$Target/Host/Property[Type="MSWL!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
  <LogName>Application</LogName>
  <Expression>
    <And>
      <Expression>
        <Or>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4956</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>4957</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5165</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5166</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5167</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5168</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5169</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5170</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5171</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5172</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5173</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
          <Expression>
            <SimpleExpression>
              <ValueExpression>
                <XPathQuery Type="Integer">EventDisplayNumber</XPathQuery>
              </ValueExpression>
              <Operator>Equal</Operator>
              <ValueExpression>
                <Value>5174</Value>
              </ValueExpression>
            </SimpleExpression>
          </Expression>
        </Or>
      </Expression>
      <Expression>
        <SimpleExpression>
          <ValueExpression>
            <XPathQuery Type="String">PublisherName</XPathQuery>
          </ValueExpression>
          <Operator>Equal</Operator>
          <ValueExpression>
            <Value>Office SharePoint Server</Value>
          </ValueExpression>
        </SimpleExpression>
      </Expression>
    </And>
  </Expression>
</Rule>'
)
)
SELECT [DisplayName]
     , R.V.value('.', 'INT') AS [EventID]
FROM dbo_table1
CROSS APPLY DataSourceXML.nodes('/Rule//Expression/SimpleExpression[ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value') AS R(V)
;

Open in new window

0
 
LVL 10

Author Closing Comment

by:Banthor
ID: 36918541
Outstanding! Yes the third was some posting difficulty.
I was able to apply the Cross apply without further modification.
Please Confirm that Brace here:
SimpleExpression[ValueExpression
finds any valueExpression under the SimpleExpression regardless of depth.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36918717
SimpleExpression[ValueExpression ...] expects that ValueExpression will be immediately below SimpleExpression and is meant to test that there is a XPathQuery element with Type attribute equal to "Integer" immediately below a  ValueExpression that is itself a direct child of SimpleExpression.

Since it appeared the variation in depth was coming before the Expression node that contains the SImpleExpression, this bit of code handles that:

/Rule//Expression

It will pick up any Expression node at any depth of Rule that has a direct child of SimpleExpression with criteria specified above in the brace.

If additionally, the ValueExpression/XPathQuery bit can be at varying levels, then you will need to adjust to something like this:

'/Rule//Expression/SimpleExpression[//ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]/ValueExpression/Value'

Just note once you do, you might grab SimpleExpression at too high a level for ValueExpression/Value to grab the correct element if you only want EventID's. For example, when I test this on the data you provided, it picks up 'Office SharePoint Server' also and because of my conversion to INT, it fails. That may be what you want though. Another way that works for only EventID's is to use XPath to get to the ValueExpression/Value then in the test, use ../../ to get to the SimpleExpression parent and then from there use the // to indicate to search any level below that.

'/Rule//Expression/SimpleExpression/ValueExpression/Value[../..//ValueExpression/XPathQuery[@Type="Integer"]="EventDisplayNumber"]'

Hope that helps!

Best regards,

Kevin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question