need to convert the xml file column to the table

Guest User

5y683

Hi all,

i am facing a problem when convert the xml file column to the table data.
Can anyone please tell me the how can i convert.
the data in the xml colunn will will be like this

DC
Date de l'index
System.DateTime
X01 08 00 date
2020;12;10;15;33;36

DD.Rate1
Rate1
System.Int64
X02 120 00
240
min

DD.Rate2
Rate2
System.Int64
X02 120 01
0
min

DD.Rate4
Rate4
System.Int64
X02 120 03
930
min

DD.Rate3
Rate3
System.Int64
X02 120 02
0
min

DD.Rate5
Rate5
System.Int64
X02 120 04
480
min

DD.Rate6
Rate6
System.Int64
X02 120 05
0
min

DD.Rate7
Rate7
System.Int64
X02 120 06
0
min

DD.Rate8
Rate8
System.Int64
X02 120 07
0
min

DQ.Rate1
Rate1
System.Int64
X02 136 00
368
kW

DQ.Rate2
Rate2
System.Int64
X02 136 01
0
kW

DQ.Rate4
Rate4
System.Int64
X02 136 03
267
kW

DQ.Rate3
Rate3
System.Int64
X02 136 02
0
kW

DQ.Rate5
Rate5
System.Int64
X02 136 04
87
kW

DQ.Rate6
Rate6
System.Int64
X02 136 05
0
kW

DQ.Rate7
Rate7
System.Int64
X02 136 06
0
kW

DQ.Rate8
Rate8
System.Int64
X02 136 07
0
kW

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User5y ago
I tried the below query but getting the null values..Pls help on this.

SELECT
X.Y.value('(Value)[1]', 'VARCHAR(20)') as Value

FROM testxml1 e
outer APPLY e.test_xml.nodes('Index/MEASURE/GroupementsInfos/InfosUnitaires') as X(Y)

+1
Aman Gupta5y ago
If you previously created an XML Map, you can use it to import XML data into cells that are mapped, but there also are several methods and commands for importing XML data without an XML Map.

If you have an XML Map, do this to import XML data into mapped cells:
1. In the XML Map, select one of the mapped cells.
2. Click Developer > Import.
  
  If you don't see the Developer tab, see Show the Developer tab.
3. In the Import XML dialog box, locate and select the XML data file (.xml) you want to import, and click Import.
0