2

Is there a way to do a Select-Object and get the values of nodes of different levels?

I need the name and a bunch of fields from the field level but also the globalFormat categoryType. Is there a way to have them in one Select-Object?

XML

<field name="FLD0000001" tableAlias="*SERVER" tableName="*SERVER">
  <order>0</order>
  <version></version>
  <afterEditEvent></afterEditEvent>
  <format></format>
  <globalFormat categoryType="Number" countrySymbolCode="" dateFormat="" timeFormat="" imageFormat="None" decimalPlaces="2" leadingZero="0" isCentury="false" negativeFormat="NegativeSymbol" />
  <columnGroupHeading></columnGroupHeading>
  <dataFieldType>20</dataFieldType>
  <columnHeading><![CDATA[FLD0000001]]></columnHeading>
  <hideHeader>False</hideHeader>
  <groupable>0</groupable>
  <subTotal>0</subTotal>
  <calculation></calculation>
  <jScriptCalculation />
  <editCalculation scriptContent="" scriptDisplayContent="">
    <tokens />
    <triggers />
  </editCalculation>
  <onCalculationEvent></onCalculationEvent>
  <onValidationEvent></onValidationEvent>
  <editableAdvanced></editableAdvanced>
  <addRequired>0</addRequired>
  <splitByRationOn></splitByRationOn>
  <defaultValue></defaultValue>
  <isCalculatable>0</isCalculatable>
  <isUpdatable>0</isUpdatable>
  <visibleAdvanced></visibleAdvanced>
  <editableLevel>0</editableLevel>
  <visibleLevel>10</visibleLevel>
  <fullTypeName></fullTypeName>
  <dataType>NUMERIC</dataType>
  <dataLength>5</dataLength>
  <description source="" format="4"></description>
  <dimensionTitle></dimensionTitle>
  <definition></definition>
  <parameterName></parameterName>
  <cubeDimensionOrder>0</cubeDimensionOrder>
  <subTotalRestrictions></subTotalRestrictions>
  <subTotalExceptions></subTotalExceptions>
  <matrixHeaderValue></matrixHeaderValue>
  <promptRestricted>0</promptRestricted>
  <prompt processId="" />
  <promptOption></promptOption>
  <sortOrderForPrompt>NONE</sortOrderForPrompt>
  <filterForPrompt></filterForPrompt>
  <relatedFields></relatedFields>
  <validateAgainstPrompt>False</validateAgainstPrompt>
  <subGroupId></subGroupId>
  <indexInGroup>0</indexInGroup>
  <widthInFieldArea>2000</widthInFieldArea>
</field>

I basically want to check dataFieldType, if it's 20 or 21, check globalFormat categoryType to see if it's none, if it is, output in a file as a Field that hasn't been set properly. but I have like hundreds of fields in that xml

Steven
  • 6,817
  • 1
  • 14
  • 14
JY.
  • 25
  • 5
  • Can you give some subset of the XML as text? BTW, I think you mean `Select-Xml`? – Steven Mar 02 '21 at 17:42
  • Sorry, I don't exactly understand what you are trying to say. I'm trying to create a Variable with all the values of all the fields in the image and get subsets of that in subsequent variables to do different QA texts to see if I've set the Fields properly, – JY. Mar 02 '21 at 18:05
  • It's generally frowned upon to post pictures of code and/or sample data. There's no way for people to test their suggestions. Can you create a sanitized but representative version of the XML for use in the question. If so please edit and replace the image with the XML... – Steven Mar 02 '21 at 18:05
  • Updated, is that good enough? – JY. Mar 02 '21 at 18:11
  • Yes that's definitely a start. I have to step away, but will look at it soon. – Steven Mar 02 '21 at 18:17

2 Answers2

2

You could use Select-Xml in combination with XPath queries and Select-Object to get the element and attribute values you need. You can specify different XPaths to get to the different node levels, including element attributes, I believe.

See these examples from Microsoft docs:

$Path = "$Pshome\Types.ps1xml"
$XPath = "/Types/Type/Members/AliasProperty"
Select-Xml -Path $Path -XPath $Xpath | Select-Object -ExpandProperty Node
Name                 ReferencedMemberName
----                 --------------------
Count                Length
Name                 Key
Name                 ServiceName
RequiredServices     ServicesDependedOn
ProcessName          Name
Handles              Handlecount
VM                   VirtualSize
WS                   WorkingSetSize
Name                 ProcessName
Handles              Handlecount
VM                   VirtualMemorySize
WS                   WorkingSet
PM                   PagedMemorySize
NPM                  NonpagedSystemMemorySize
Name                 __Class
Namespace            ModuleName

For example, if you wanted to get the attributes of the globalFormat element you would do something like this:

$path = "\test.xml"
$xpath = "/field/globalFormat"
Select-Xml -Path $path -XPath $xpath | Select-Object -ExpandProperty Node

categoryType      : Number
countrySymbolCode :
dateFormat        :
timeFormat        :
imageFormat       : None
decimalPlaces     : 2
leadingZero       : 0
isCentury         : false
negativeFormat    : NegativeSymbol

If you want to access different elements and attributes at the same time, you can do something like this:

$path = "test.xml"
$xpathField = "/field"
$xpathGlobalFormat = "/field/globalFormat"
$field = Select-Xml -Path $path -XPath $xpathField | Select-Object -ExpandProperty Node
$globalFormat = Select-Xml -Path $path -XPath $xpathGlobalFormat | Select-Object -ExpandProperty Node
$field.tableName # this returns *SERVER
$globalFormat.categoryType # this returns Number
Alex Riveron
  • 389
  • 1
  • 10
  • Tried but I'm getting the same result as what I was originally getting (nodes at field level but not the childs (or is it properties?) of globalFormat) with the xpath "fields/field". If I add a * after in the xpath then I'm getting a weird result with nodes outside field coming out... Am I doing anything wrong? – JY. Mar 02 '21 at 20:25
  • @JY see my edited answer. I tried answering in the comment, but didn't realize the markdown was limited. Also, see Steven's answer, it looks like he's expanded on mine a bit. – Alex Riveron Mar 02 '21 at 20:32
  • I managed to get a similar result too but I lose access to things like tablealias and tablename, is there a way to get them all in one Select? – JY. Mar 02 '21 at 21:01
  • @JY See my edited answer, the last example shows how to access the field element attributes as well as the globalFormat attributes without "losing access" to them. There are probably other approaches, butt this approach just stores the object returned into variables so that you can access the attributes and use them in other parts of your script. – Alex Riveron Mar 02 '21 at 21:11
  • Problem with that is I have over 100 xmls to check and everyone of them have over 100 "field" node. If I bring them like that, they are out of sync I think since they are 2 different objects. Should I just do a loop on the field node and use this method instead? – JY. Mar 02 '21 at 21:49
  • You could create a hash table, `@{}` for each field, and populate it with the attribute names and values you're interested in, and then add each of them to an array, `@()`, while you loop through each of the 100 XML files. You'll end up with an array of hash tables, one for each file, that you can use elsewhere in your script. Or you could also create a hash table of hash tables, where the key of the top level hash table would be a unique identifier for each of the XML files, and the value of the top hash table would be the hash table with all the attributes for a specific XML file. – Alex Riveron Mar 03 '21 at 02:12
2

To build on Alex's helpful answer I think you are looking for an XPath query you can leverage to get the information in one shot. I was thinking something like:

(Select-Xml -Xml $Xml -XPath "/field/@* | /field/globalFormat/@categoryType").Node

I'm not exactly sure what output you are looking for, but another option would be to simply select the field nodes and just use "." referencing to get at the sub-properties to construct custom objects.

To get all of the elements and attributes under /field AND /field/globalFormat:

(Select-Xml -Xml $Xml -XPath '/field | /field/globalFormat').Node
Steven
  • 6,817
  • 1
  • 14
  • 14
  • Ah, the output I'm expecting is all the values of the field level (name, tablealiasname, tablename, etc) + categoryType (I only really need this one but it doesn't hurt to have more) for each field that exist. – JY. Mar 02 '21 at 20:53
  • I presume you can put as many `|`'s in the XPath query as you want. I think what's throwing us is you want everything in 1 statement. – Steven Mar 02 '21 at 21:26
  • Having everything in one statement makes things easier but I guess I need an alternative if it gets complicated. I have over 100 xmls with over 100 field nodes each so I wanted to bring them as one "array" or "row" to make it easier to check with the where option. – JY. Mar 02 '21 at 21:51
  • Honestly, I'm a beginner at powershell and I guess I'm trying to bring it a more sql format to make easier for me to code. – JY. Mar 02 '21 at 21:53
  • Ah, sorry, I tried that at the end of my day and I wasn't getting the results that I want but it's because I forgot to change your xpath since my xml is different from yours, this worked perfectly, thank you. – JY. Mar 03 '21 at 14:55