none
Populating table from XML RRS feed

  • Question

  • Hi,

    I have the following XML File

    <ReadCodes>
    	<Diabeties>
    		<Code description="Generic Diabeties">C10.00</Code>
    	    	<Code description="Type1 Diabeties">C108.00</Code>
    		<Code description="Type2 Diabeties">C109.00</Code>
      	</Diabeties>
    	<IHD>
    		<Code>G3.00</Code>
    	</IHD>
    	<COPD>
    		<Code>H3.00</Code>
    	</COPD>
    	<Asthma>
    		<Code>H33.00</Code>
    	</Asthma>
    	<TIACVA>
    		<Code>G65.00</Code>
    		<Code>G66.00</Code>
    	</TIACVA>
    	<Hypertension>
    		<Code>CG20.00</Code>
    	</Hypertension>
    	<GluecoseIntolerance>
    		<Code>C3135.00</Code>
    	</GluecoseIntolerance>
    	<Lipids>
    		<Code>C324.00</Code>
    	</Lipids>
    </ReadCodes>
    
    
    I have a table created using the following structure


    CREATE
    TABLE [dbo].[LTC_CODES]( [Code] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Condition] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_LTC_CODES] PRIMARY KEY CLUSTERED ( [Code] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
    The mapping would be:

    Code = Code element (e.g. C10.00)
    Condition = Parent Node (e.g. Diabeties)
    Decription = description attribute (e.g. Generic Diabeties)

    I was going to write some code to read the XML node by node and insert a record for each one. something like this...

                    XmlDocument doc = new XmlDocument();
                    doc.Load(AppDomain.CurrentDomain.BaseDirectory + "readcodes.xml");
                    XmlElement root = doc.DocumentElement;
                    XmlNodeList nodes = root.SelectNodes("/ReadCodes");
    
                    foreach (XmlNode node in nodes)
                    {
                        foreach (XmlNode LTCnode in node)
                        {
                            foreach (XmlNode codenode in LTCnode)
                            {
                                create record insert here...
                            }
                        }
                    }
    
    
    

    ...but I have a feeling there may be an easier way.

    Can anyone help :-)

    Cheers,
    Kevin. 
    • Edited by Elroacho Monday, June 22, 2009 2:29 AM
    Monday, June 22, 2009 2:22 AM

Answers

  • With LINQ to SQL and LINQ to XML, you can do this much more easily:

        XElement readCodes = XElement.Load (Path.Combine (AppDomain.CurrentDomain.BaseDirectory, "readcodes.xml" ));
        
        
    var newRows =
            
    from category in readCodes.Elements()
            
    from code in category.Elements()
            
    select new Ltc_Code { Code = code.Value, Condition = category.Name, Description = ( string ) code.Attribute ( "description" ) };
        
        
    var db = new MyMedicalDataContext();
        db.Ltc_Codes.InsertAllOnSubmit (newRows);
        db.SubmitChanges();


    You'll need to create a typed LINQ-to-SQL DataContext to make this work (I've called it MyMedicalDataContext in this example).

    This will work well if your XML file is relatively small (maybe a few hundred rows). If you wanted to upload the entire Read Code set, then you'll need to think differently. For a start, you won't want to load the entire XML file into memory at once (the entire Read Code set is massive), so you should use XmlReader instead. Second, you'll want to use the SqlBulkCopy class or the bcp utilitity to upload into the database. To make the former easier, there's a trick where you can combine XmlReader with XElement: Use XmlReader to read the outer elements and the use XElement to read the inner ones.

         using (XmlReader r = XmlReader.Create (Path.Combine (AppDomain.CurrentDomain.BaseDirectory, "readcodes.xml" ), settings))
        {
            r.ReadStartElement (
    "ReadCodes" );
            
    while (!r.EOF)
            {
                XElement condition = (XElement) XNode.ReadFrom (r);
                
    // Now you can work with the condition node as an XElement...

                ...

            }
            r.ReadEndElement();
        }


    As a final note, I seem to remember Read Codes were case-sensitive last time I worked with them. If this is the case, you should change the SQL collation on your column as follows:

       ... [Code] [varchar](15) COLLATE SQL_Latin1_General_CP1_CS _AS NOT NULL


    Joe
    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by Elroacho Monday, June 22, 2009 4:23 AM
    Monday, June 22, 2009 3:06 AM
    Answerer

All replies

  • With LINQ to SQL and LINQ to XML, you can do this much more easily:

        XElement readCodes = XElement.Load (Path.Combine (AppDomain.CurrentDomain.BaseDirectory, "readcodes.xml" ));
        
        
    var newRows =
            
    from category in readCodes.Elements()
            
    from code in category.Elements()
            
    select new Ltc_Code { Code = code.Value, Condition = category.Name, Description = ( string ) code.Attribute ( "description" ) };
        
        
    var db = new MyMedicalDataContext();
        db.Ltc_Codes.InsertAllOnSubmit (newRows);
        db.SubmitChanges();


    You'll need to create a typed LINQ-to-SQL DataContext to make this work (I've called it MyMedicalDataContext in this example).

    This will work well if your XML file is relatively small (maybe a few hundred rows). If you wanted to upload the entire Read Code set, then you'll need to think differently. For a start, you won't want to load the entire XML file into memory at once (the entire Read Code set is massive), so you should use XmlReader instead. Second, you'll want to use the SqlBulkCopy class or the bcp utilitity to upload into the database. To make the former easier, there's a trick where you can combine XmlReader with XElement: Use XmlReader to read the outer elements and the use XElement to read the inner ones.

         using (XmlReader r = XmlReader.Create (Path.Combine (AppDomain.CurrentDomain.BaseDirectory, "readcodes.xml" ), settings))
        {
            r.ReadStartElement (
    "ReadCodes" );
            
    while (!r.EOF)
            {
                XElement condition = (XElement) XNode.ReadFrom (r);
                
    // Now you can work with the condition node as an XElement...

                ...

            }
            r.ReadEndElement();
        }


    As a final note, I seem to remember Read Codes were case-sensitive last time I worked with them. If this is the case, you should change the SQL collation on your column as follows:

       ... [Code] [varchar](15) COLLATE SQL_Latin1_General_CP1_CS _AS NOT NULL


    Joe
    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by Elroacho Monday, June 22, 2009 4:23 AM
    Monday, June 22, 2009 3:06 AM
    Answerer
  • Worked a charm!

    I am only working with a small subset of readcodes, currently all you see in that example so this methond will work well.

    Cheers for your help.

    PS. Saying you have worked with read codes I assume you have worked on medical software. Is this in General Practice?

    Monday, June 22, 2009 4:23 AM
  • Yes, in General Practice (and on two occasions: in New Zealand and for a company in the UK).

    Write LINQ queries interactively - www.linqpad.net
    Monday, June 22, 2009 6:40 AM
    Answerer
  • Cool,

    What work did you do in NZ?

    That's where I am based.

    Kevin.
    Monday, June 22, 2009 7:19 AM
  • I was one of the founders of Houston Medical. Although that was a long time ago!

    Which company do you work for? How many are still going?

    Write LINQ queries interactively - www.linqpad.net
    Monday, June 22, 2009 11:37 AM
    Answerer