How to read XML-Data into a dataTable via Powershell and force using correc types via a given XmlSchema?

Question

I am currently struggling to read XMLdata into a dataTable by enforcing a given XmlSchema. Whatever I do, after the Data-Import all Types are set back to "string". I need to force the below ID-column to be of type "int" (not "string" or "byte"):

$schema = '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:group name="r">
    <xs:sequence>
      <xs:element name="id" type="xs:int" />
      <xs:element name="name" type="xs:string" />
    </xs:sequence>
  </xs:group>
  <xs:complexType name="body">
    <xs:group ref="r" />
  </xs:complexType>
</xs:schema>'

$data = '
<body>
    <r>
        <id>9</id>
        <name>AAA</name>
    </r>
    <r>
        <id>10</id>
        <name>BBB</name>
    </r>
</body>'

# read the schema:
$set = [System.Data.Dataset]::new()
$sr =[System.IO.StringReader]::new($schema)
$set.ReadXmlSchema($sr)

# read the data:
$sr =[System.IO.StringReader]::new($data)
$set.ReadXml($sr)

cls
$set.Tables | ft -AutoSize
write-host "type of column 'id' in table : " $set.Tables[0].Columns[0].DataType

$list = [System.Collections.ArrayList]::new($set.Tables[0].GetList())
write-host "type of column 'id' in list  : " $list[0].id.GetType()

I also did some tests with the XMLReadMode, but the only change appears, when I use [System.Data.XmlReadMode]::InferTypedSchema, but this changes the type to "byte" or something else depending on the data for that column.

Any help is more than welcome here! Thanks you in advance.

See https://learn.microsoft.com/en-us/archive/blogs/marcelolr/dataset-does-not-validate-xml-schema-xsd - "If you're working with the DataSet class and loading and saving data in XML format, you may have run into a bit of a surprise. Even when you specifically read an XSD schema into your DataSet, when you load data into it, it seems that validation doesn't work - you're still able to pass in data with extra fields that may get ignored or have found other surprising results." — mclayton, Jul 15 '20 at 15:05
Also, if you use an online validator, you'll find your xml example isn't valid according to your schema. E.g. ```Cannot Find The Declaration Of Element 'body'., Line '1', Column '7'.``` here: https://www.freeformatter.com/xml-validator-xsd.html — mclayton, Jul 15 '20 at 15:11

Carsten · Accepted Answer · 2020-07-18T14:58:24.793

Thanks to the hints I finally solved it. First I found out, that I dont need to create a dedicated XMLSchema before the data-import - just creating the proper table-structure did the same thing with less code.

Here the final code-sample:

cls
$set =[System.Data.DataSet]::new()
$set.tables.Add('r')
$table = $set.Tables[0]
[void]$table.Columns.Add('id', [int])
[void]$table.Columns.Add('name', [string])

$data = '
<body>
    <r>
        <id>9</id>
        <name>AAA</name>
    </r>
    <r>
        <id>10</id>
        <name>BBB</name>
    </r>
</body>'

# read the data:
$sr =[System.IO.StringReader]::new($data)
[void]$set.ReadXml($sr)

$set.Tables | ft -AutoSize
write-host "type of column 'id' in table : "   $table.Columns['id'].DataType

$list = [System.Collections.ArrayList]::new($table.GetList())
write-host "type of column 'id' in list  : " $list[0].id.GetType()

Here the output from above script:

id name
-- ----
 9 AAA 
10 BBB 


type of column 'id' in table :  System.Int32
type of column 'id' in list  :  System.Int32

How to read XML-Data into a dataTable via Powershell and force using correc types via a given XmlSchema?

1 Answers1