Create Table using Xml Files

Question

Create Table using Xml Files

Geoffrey SOUQUIÈRE 51

Hi There,

Hope you are getting well.

I was wondering, is there a way to use xml file as input to create table automatically.

Here is the schema, we got all information we need to create tables and columns. Schema comes from an url Odata/$metadata

Thx,
Have a nice day

2 answers

Your answer

Answer 1

GeethaThatipatri-MSFT 29,542 Microsoft Employee Moderator

Hi, @Geoffrey SOUQUIÈRE Thanks for posting your question in the Microsoft Q&A forum.
Please find this blog which helps you to convert XML to table https://www.c-sharpcorner.com/blogs/sql-query-to-convert-xml-to-table

Please let me know if you need any additional information.

Regards
Geetha

Geoffrey SOUQUIÈRE 51 Reputation points

2022-06-01T08:02:29.017+00:00

Hi @GeethaThatipatri-MSFT ,

Thank you for your answer.

So I tried DECLARE @XMLData XML SET @XMLData = '[...]'

The code worked, but it's seems that no table had been created...

My goal is :

Input xml
Read xml
Create table based on value read

Example this will create BuyerParty table with spec (ObjectID, ParentObjectID, PartyID...)

<EntityType Name="BuyerParty">
<Key>
<PropertyRef Name="ObjectID"/>
</Key>
<Property Name="ObjectID" Type="Edm.String" Nullable="false" MaxLength="70" FixedLength="true" sap:creatable="false" sap:updatable="false" sap:filterable="true"/>
<Property Name="ParentObjectID" Type="Edm.String" Nullable="true" MaxLength="70" FixedLength="true" sap:creatable="true" sap:updatable="false" sap:filterable="true"/>
<Property Name="PartyID" Type="Edm.String" Nullable="true" MaxLength="60" FixedLength="true" sap:creatable="false" sap:updatable="false" sap:filterable="true"/>
<NavigationProperty Name="CustomerInvoice" Relationship="cust.CustomerInvoice_BuyerParty" FromRole="BuyerParty" ToRole="CustomerInvoice"/>
<NavigationProperty Name="BuyerPartyName" Relationship="cust.BuyerParty_BuyerPartyName" FromRole="BuyerParty" ToRole="BuyerPartyName"/>
<NavigationProperty Name="BuyerPartyFormattedAddress" Relationship="cust.BuyerParty_BuyerPartyFormattedAddress" FromRole="BuyerParty" ToRole="BuyerPartyFormattedAddress"/>
</EntityType>

And I will insert values later.

Let me know,
Have a nice day
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-01T08:53:04.363+00:00

@Geoffrey SOUQUIÈRE Thanks for the response and for sharing additional information.
I haven't tried to reproduce but I think you have to create the table manually based on the xml data and load it.
I will try from my end and provide you with more details.

Regards
Geetha
Geoffrey SOUQUIÈRE 51 Reputation points

2022-06-01T09:17:51.653+00:00

@GeethaThatipatri-MSFT Unfortunate.

I was really into creating table using xml schema and not doing it manually (no value added). Plus in the xml there is no data, only architecture & spec; everything to create tables manually but wanted it automatically.

Let me know if you got something and thx for your time on this issue
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-01T23:26:29.687+00:00

@Geoffrey SOUQUIÈRE
I took a quick look at the logic and once I formatted the XML to be actual XML, it worked fine for me.
DECLARE @XMLData XML
SET @XMLData = '<STUDENTS><STUDENT><StudentID>1</StudentID><Name>John Smith</Name><Marks>200</Marks></STUDENT></STUDENTS>'
SELECT @XMLData
SELECT StudentID = Node.Data.value('(StudentID)1', 'INT'), [Name] = Node.Data.value('(Name)1', 'VARCHAR(MAX)'), [Marks] = Node.Data.value('(Marks)1', 'INT')
FROM @XMLData.nodes('/STUDENTS/STUDENT') Node(Data)

Regards
Geetha

Answer 2

Hi @GeethaThatipatri-MSFT ,

Thx for testing the code.

My goal is to create table without data regarding xml schema.

I suppose there is not such a thing on SQL... Unfortunate

If i take the example below :

XML (Input)

SQL (Output)

Create table BuyerParty

I didn't write the FK associate to BuyerPartyName & BuyerPartyFormattedAddress.

Have a nice day

Share via

Create Table using Xml Files

2 answers

Your answer