Need help importing data from XML file

PowerShell Help
1

Hi!

This is my first attempt to import data from XML to a SQL server using Powershell and I could use some help :slight_smile:

The XML data is output from a PACS system (C-find command) stuctured like this example:

<responses type="C-FIND">
  <data-set xfer="1.2.840.10008.1.2" name="Little Endian Implicit">
    <element tag="0008,0005" vr="CS" vm="1" len="10" name="SpecificCharacterSet">ISO_IR 192</element>
    <element tag="0008,0020" vr="DA" vm="1" len="8" name="StudyDate">20230605</element>
    <element tag="0008,0030" vr="TM" vm="1" len="6" name="StudyTime">063356</element>
    <element tag="0008,0050" vr="SH" vm="1" len="12" name="AccessionNumber">123546</element>
    <element tag="0008,0052" vr="CS" vm="1" len="6" name="QueryRetrieveLevel">STUDY</element>
    <element tag="0008,0054" vr="AE" vm="1" len="12" name="RetrieveAETitle">CFIND</element>
    <element tag="0008,0061" vr="CS" vm="1" len="2" name="ModalitiesInStudy">US</element>
    <element tag="0008,1030" vr="LO" vm="1" len="12" name="StudyDescription">mammografiB</element>
    <element tag="0010,0010" vr="PN" vm="1" len="28" name="PatientName">Doe^John^^^</element>
    <element tag="0010,0020" vr="LO" vm="1" len="10" name="PatientID">1234567890</element>
    <element tag="0020,0010" vr="SH" vm="1" len="16" name="StudyID">ABCDEFG</element>
  </data-set>
  <data-set xfer="1.2.840.10008.1.2" name="Little Endian Implicit">
    <element tag="0008,0005" vr="CS" vm="1" len="10" name="SpecificCharacterSet">ISO_IR 192</element>
    <element tag="0008,0020" vr="DA" vm="1" len="8" name="StudyDate">20230605</element>
    <element tag="0008,0030" vr="TM" vm="1" len="6" name="StudyTime">070104</element>
    <element tag="0008,0050" vr="SH" vm="1" len="12" name="AccessionNumber">BCDE</element>
    <element tag="0008,0052" vr="CS" vm="1" len="6" name="QueryRetrieveLevel">STUDY</element>
    <element tag="0008,0054" vr="AE" vm="1" len="12" name="RetrieveAETitle">CFIND</element>
    <element tag="0008,0061" vr="CS" vm="1" len="2" name="ModalitiesInStudy">MR</element>
    <element tag="0008,1030" vr="LO" vm="1" len="20" name="StudyDescription">Hand</element>
    <element tag="0010,0010" vr="PN" vm="1" len="22" name="PatientName">Smith^Sue^^^</element>
    <element tag="0010,0020" vr="LO" vm="1" len="10" name="PatientID">1223342170</element>
    <element tag="0020,0010" vr="SH" vm="1" len="16" name="StudyID">BCDEF</element>
  </data-set>
</responses>

Each of the element values in a data-set should be imported as one row into a SQL table with the following table definition:

CREATE TABLE Migrations (
 	SpecificCharacterSet nvarchar(40) NOT NULL,
 	StudyDate int NOT NULL,
 	StudyTime  int NOT NULL,
 	AccessionNumber nvarchar(30) NOT NULL,
 	QueryRetrieveLevel nvarchar(10) NOT NULL,
 	RetrieveAETitle nvarchar(16) NOT NULL,
 	ModalitiesInStudy nvarchar(10) NOT NULL,
 	StudyDescription nvarchar(255) NOT NULL,
 	PatientName nvarchar(255) NOT NULL,
 	PatientID nvarchar(50) NOT NULL,
 	StudyID nvarchar(50) NOT NULL
 	CONSTRAINT [PK_Studies] PRIMARY KEY CLUSTERED 
 	(
 		[StudyID] ASC
 	)
 )

I created an XMLSchema based on the actual data:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="dsMigration" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
   <xs:element name="responses">
     <xs:complexType>
       <xs:sequence>
         <xs:element name="data-set" minOccurs="0" maxOccurs="unbounded">
           <xs:complexType>
             <xs:sequence>
               <xs:element name="element" nillable="true" minOccurs="0" maxOccurs="unbounded">
                 <xs:complexType>
                   <xs:simpleContent msdata:ColumnName="element_Text" msdata:Ordinal="5">
                     <xs:extension base="xs:string">
                       <xs:attribute name="tag" type="xs:string" />
                       <xs:attribute name="vr" type="xs:string" />
                       <xs:attribute name="vm" type="xs:string" />
                       <xs:attribute name="len" type="xs:string" />
                       <xs:attribute name="name" type="xs:string" />
                     </xs:extension>
                   </xs:simpleContent>
                 </xs:complexType>
               </xs:element>
             </xs:sequence>
             <xs:attribute name="xfer" type="xs:string" />
             <xs:attribute name="name" type="xs:string" />
           </xs:complexType>
         </xs:element>
       </xs:sequence>
       <xs:attribute name="type" type="xs:string" />
     </xs:complexType>
   </xs:element>
   <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
     <xs:complexType>
       <xs:choice minOccurs="0" maxOccurs="unbounded">
         <xs:element ref="responses" />
       </xs:choice>
     </xs:complexType>
   </xs:element>
 </xs:schema>

This is my current PS code.

$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=SQL-server;Integrated Security=SSPI;Initial Catalog=Migration");
$cn.Open()

# Load and use schema
$ds = new-object "System.Data.DataSet" "dsMigration"
$ds.ReadXmlSchema('C:\DICOM\xsd\20230612.xsd')

# Read the XML file and set data in migration table
$ds.ReadXml("C:\DICOM\20230612.xml")
$dtMigration = $ds.Tables[2]

# Create bulk copy object and write data
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.DestinationTableName = "dbo.Migrations"
$bc.WriteToServer($dtMigration)

$cn.Close()

I am really at my very first attempt, so feel free to suggest other approaches.

  1. $ds.ReadXml("C:\DICOM\20230612.xml")
    returns “IgnoreSchema” - which indicates something is not right.

  2. $dtMigration = $ds.Tables[2]
    I feel I’m not right on this one :wink:

  3. $bc.WriteToServer($dtMigration)
    This fails due to incorrect datatype. I assume it is related to the issue above.

Any help is appreciated!

Thank you :slight_smile:

/JP

2

Trying to parse the XML with XSD is a challenging to see if it’s working properly. Another approach would be parsing into an object and then you can use Write-SQLTable or Invoke-SqlCmd to write the data into SQL.

[xml]$xml = @"
<responses type="C-FIND">
  <data-set xfer="1.2.840.10008.1.2" name="Little Endian Implicit">
    <element tag="0008,0005" vr="CS" vm="1" len="10" name="SpecificCharacterSet">ISO_IR 192</element>
    <element tag="0008,0020" vr="DA" vm="1" len="8" name="StudyDate">20230605</element>
    <element tag="0008,0030" vr="TM" vm="1" len="6" name="StudyTime">063356</element>
    <element tag="0008,0050" vr="SH" vm="1" len="12" name="AccessionNumber">123546</element>
    <element tag="0008,0052" vr="CS" vm="1" len="6" name="QueryRetrieveLevel">STUDY</element>
    <element tag="0008,0054" vr="AE" vm="1" len="12" name="RetrieveAETitle">CFIND</element>
    <element tag="0008,0061" vr="CS" vm="1" len="2" name="ModalitiesInStudy">US</element>
    <element tag="0008,1030" vr="LO" vm="1" len="12" name="StudyDescription">mammografiB</element>
    <element tag="0010,0010" vr="PN" vm="1" len="28" name="PatientName">Doe^John^^^</element>
    <element tag="0010,0020" vr="LO" vm="1" len="10" name="PatientID">1234567890</element>
    <element tag="0020,0010" vr="SH" vm="1" len="16" name="StudyID">ABCDEFG</element>
  </data-set>
  <data-set xfer="1.2.840.10008.1.2" name="Little Endian Implicit">
    <element tag="0008,0005" vr="CS" vm="1" len="10" name="SpecificCharacterSet">ISO_IR 192</element>
    <element tag="0008,0020" vr="DA" vm="1" len="8" name="StudyDate">20230605</element>
    <element tag="0008,0030" vr="TM" vm="1" len="6" name="StudyTime">070104</element>
    <element tag="0008,0050" vr="SH" vm="1" len="12" name="AccessionNumber">BCDE</element>
    <element tag="0008,0052" vr="CS" vm="1" len="6" name="QueryRetrieveLevel">STUDY</element>
    <element tag="0008,0054" vr="AE" vm="1" len="12" name="RetrieveAETitle">CFIND</element>
    <element tag="0008,0061" vr="CS" vm="1" len="2" name="ModalitiesInStudy">MR</element>
    <element tag="0008,1030" vr="LO" vm="1" len="20" name="StudyDescription">Hand</element>
    <element tag="0010,0010" vr="PN" vm="1" len="22" name="PatientName">Smith^Sue^^^</element>
    <element tag="0010,0020" vr="LO" vm="1" len="10" name="PatientID">1223342170</element>
    <element tag="0020,0010" vr="SH" vm="1" len="16" name="StudyID">BCDEF</element>
  </data-set>
</responses>
"@

$obj = foreach ($elem in $xml.responses.'data-set') {
    $properties = @{}
    foreach($prop in $elem.element) {
        $properties.Add($prop.name, $prop.'#text')
    }

    [pscustomobject]$properties
}

$obj

#Write-SqlTableData -InputData $obj ...
1 Like
3

Your solution worked very well and was easy to implement.

Thank you very much!