XML File with nested relations - Error

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!

Answer 1

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();

Answer 2

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

Answer 3

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

Answer 4

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...

Answer 5

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

Answer 6

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