locked
XML File with nested relations - Error RRS feed

  • Question

  • User430786638 posted

    I have an XML file which includes the following structure (which is an ISO standard for SEPA pain.001.001.02)

    <Cdtr>

    <Nm>Cient Name Surname 01278</Nm>

    <PstlAdr>

    <Ctry>XX</Ctry>

    <AdrLine>Address Line 1</AdrLine>

    <AdrLine>Address Line 2</AdrLine>

    </PstlAdr>

    </Cdtr>

    I am having problems loading into a dataset because the AdrLine is repeated.

    Error: Cannot add constraint to DataTable 'Pst lAdr' which is a child table in two nested relations.

    How can I solve the problem? I cannot rename the AdrLine tags because they are required by the client when there are multiple address lines.

    Thanks!

    Wednesday, October 23, 2013 3:51 AM

Answers

  • User697462465 posted

    Hi jpattard,

    We can convert the string type of your xml to Stream type, then use the ReamXML method of DataSet.

    About the convert string to Stream please refer to:

    // convert string to stream
                byte[] byteArray = Encoding.UTF8.GetBytes("");
                //byte[] byteArray = Encoding.ASCII.GetBytes(contents);
                MemoryStream stream = new MemoryStream(byteArray);

    Hope it helps.

    Best Regards,
    Terry Guo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 28, 2013 3:05 AM
  • User430786638 posted

    Thanks for all your replies!

    I managed to do the Job by loading the sql output to an XMLDocument as below...it works perfectly for my needs.

    Dim MyCommand As SqlCommand = New SqlCommand("exec sqlSP ", MyConnection)
    Dim rdr As XmlReader = MyCommand.ExecuteXmlReader()
    
    
    If (rdr IsNot Nothing) Then
    rdr.Read()
    Dim doc As New XmlDocument()
    
    doc.Load(rdr)
    
    doc.InsertBefore(doc.CreateXmlDeclaration("1.0", "UTF-8", ""), doc.FirstChild)
    
    Dim ns1 As String = "urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"
    Dim xsi As String = "http://www.w3.org/2001/XMLSchema-instance"
    
    
    doc.DocumentElement.SetAttribute("xmlns:xsi", xsi)
    doc.DocumentElement.SetAttribute("xmlns", ns1)
    
    doc.Save(filename) ' save data in current domain
    
    End If



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 6, 2013 3:02 AM

All replies

  • User1508394307 posted

    How do you load exactly?

    This works for me

    String xml = @"<Cdtr>

    <Nm>Cient Name Surname 01278</Nm>

    <PstlAdr>

    <Ctry>XX</Ctry>

    <AdrLine>Address Line 1</AdrLine>

    <AdrLine>Address Line 2</AdrLine>

    </PstlAdr>

    </Cdtr>";

    StringReader sr = new StringReader(xml);
    DataSet ds = new DataSet();
    ds.ReadXml(sr);
    GridView1.DataSource = ds;
    GridView1.DataBind();

    Wednesday, October 23, 2013 9:06 AM
  • User430786638 posted

    The file is loaded through the output of an SQL SP using the FORxml command...

    and then I read the output using the following line:

    Try
    MyConnection.Open()

    Dim MyCommand As SqlCommand = New SqlCommand("exec XMLSP", MyConnection)

    ds.ReadXml(MyCommand.ExecuteXmlReader(), XmlReadMode.Auto)

    ds.WriteXml(filename)


    Catch ex As Exception
    Response.Write(ex.Message)
    End Try

    Wednesday, October 23, 2013 10:30 AM
  • User697462465 posted

    Hi jpattard,

    Try to use the following code:

    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("XmlFile.xml"));

    The ReadXml of DataSet will sperate the sample element to a table.

    Hope it helps.

    Best Regards,
    Terry Guo

    Thursday, October 24, 2013 2:44 AM
  • User430786638 posted

    Hello Terry,

    Unfortunately, your soulution does not apply to my problem, because my XML is not in a file but I am loading it directly from SQL...

    Thursday, October 24, 2013 3:14 AM
  • User697462465 posted

    Hi jpattard,

    We can convert the string type of your xml to Stream type, then use the ReamXML method of DataSet.

    About the convert string to Stream please refer to:

    // convert string to stream
                byte[] byteArray = Encoding.UTF8.GetBytes("");
                //byte[] byteArray = Encoding.ASCII.GetBytes(contents);
                MemoryStream stream = new MemoryStream(byteArray);

    Hope it helps.

    Best Regards,
    Terry Guo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 28, 2013 3:05 AM
  • User430786638 posted

    Thanks for all your replies!

    I managed to do the Job by loading the sql output to an XMLDocument as below...it works perfectly for my needs.

    Dim MyCommand As SqlCommand = New SqlCommand("exec sqlSP ", MyConnection)
    Dim rdr As XmlReader = MyCommand.ExecuteXmlReader()
    
    
    If (rdr IsNot Nothing) Then
    rdr.Read()
    Dim doc As New XmlDocument()
    
    doc.Load(rdr)
    
    doc.InsertBefore(doc.CreateXmlDeclaration("1.0", "UTF-8", ""), doc.FirstChild)
    
    Dim ns1 As String = "urn:iso:std:iso:20022:tech:xsd:pain.001.001.02"
    Dim xsi As String = "http://www.w3.org/2001/XMLSchema-instance"
    
    
    doc.DocumentElement.SetAttribute("xmlns:xsi", xsi)
    doc.DocumentElement.SetAttribute("xmlns", ns1)
    
    doc.Save(filename) ' save data in current domain
    
    End If



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 6, 2013 3:02 AM