XML Parsing -Convert Table Rows /Columns inside XML to text

• Bala'

  Ten Centuries

  Points: 1186

  More actions

  January 18, 2018 at 11:14 pm

  #341829

  I have Xml like below which got (Table (one or more) , columns , rows ) ..It needs to be converted to txt like below 

  XML format (below as table) 
  Table Name -1

  Column1	Column2	Column3
  R1C1	R1C2	R1C3
  R2C1	R2C2	R2C3

  Table Name -2

  Column1	Column2	Column3
  R1C1	R1C2	R1C3

  Covert To text 
  
  Table Name -1 
  Column1: R1C1
  Column2: R1C2
  Column3: R1C3

  Column1: R2C1
  Column2: R2C2
  Column3: R2C3

  Table Name -2 

  Column1: R1C1
  Column2: R1C2
  Column3: R1C3

  Sample SQL Server - Inserts in table column 

  create table XMLwithOpenXML ( rawXMLId int identity(1,1) , XMLData xml ,TxtData varchar(max) )

  delete from XMLwithOpenXML
insert into XMLwithOpenXML (XMLData)
select '<APPMANAGER>
<DATADETAIL>
  <TABLE NAME="Table1" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table1 Discription">
  <EXTENDEDNAME>Table1 Desc Report</EXTENDEDNAME>
  <CDEF>
   <C0 NAME="Table1 Col1" TYPE="String" />
   <C1 NAME="Table1 Col2" TYPE="String" />
   <C2 NAME="Table1 Col3" TYPE="String" />
  </CDEF>
  <R>
   <C0 DataType="RawData">Table1 l11</C0>
   <C1 DataType="RawData">Table1 l12</C1>
   <C2 DataType="RawData">Table1 l13</C2>
  </R>
  <R>
   <C0 DataType="RawData">Table1 l21</C0>
   <C1 DataType="RawData">Table1 l22</C1>
   <C2 DataType="RawData">Table1 l23</C2>
  </R>
  </TABLE>
  <TABLE NAME="Table2" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table2 Desc">
  <EXTENDEDNAME>Table2 Desc Report</EXTENDEDNAME>
  <CDEF>
   <C0 NAME="Table2 Col1" TYPE="String" />
   <C1 NAME="Table3 Col2" TYPE="String" />
   <C2 NAME="Table3 Col3" TYPE="String" />
  </CDEF>
  <R>
   <C0 DataType="RawData">Table2 l11</C0>
   <C1 DataType="RawData">Table2 l12</C1>
   <C2 DataType="RawData">Table2 l13</C2>
  </R>
  </TABLE>
</DATADETAIL>
</APPMANAGER>'
union
select '
<APPMANAGER>
<DATADETAIL>
  <TABLE NAME="Table1" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table1 Discription">
  <EXTENDEDNAME>Table1 Desc Report</EXTENDEDNAME>
  <CDEF>
   <C0 NAME="Table1 Col1" TYPE="String" />
   <C1 NAME="Table1 Col2" TYPE="String" />
   <C2 NAME="Table1 Col3" TYPE="String" />
  </CDEF>
  <R>
   <C0 DataType="RawData">Table1 l11</C0>
   <C1 DataType="RawData">Table1 l12</C1>
   <C2 DataType="RawData">Table1 l13</C2>
  </R>
  <R>
   <C0 DataType="RawData">Table1 l21</C0>
   <C1 DataType="RawData">Table1 l22</C1>
   <C2 DataType="RawData">Table1 l23</C2>
  </R>
  </TABLE>
</DATADETAIL>
</APPMANAGER>'


  I am able to extract the values in the XML from below XQuery 

  ;WITH cte ( rawXMLId ,tablename,TableNumber ,tableXML) AS (
SELECT
  t.rawXMLId,
    tableset.c.value( '@NAME', 'VARCHAR(MAX)' ),
  ROW_NUMBER() OVER ( PARTITION BY tableset.c.value( '@NAME', 'VARCHAR(MAX)' ) ORDER BY ( SELECT NULL ) ) TableNumber ,
    tableset.c.query('.') rowXML
FROM XMLwithOpenXML t
  CROSS APPLY XMLData.nodes('/APPMANAGER/DATADETAIL/TABLE') tableset(c)
)
SELECT
  c.rawXMLId,
  c.TableNumber,
 tablename,
    ROW_NUMBER() OVER ( PARTITION BY rowset.c.value( '@NAME', 'VARCHAR(MAX)' ) ORDER BY ( SELECT NULL ) ) RowNumber,
  rowset.c.value('./text()[1]', 'VARCHAR(50)') AS cellData,
    ColumnSet.c.value('./@NAME', 'varchar(100)') AS Element
--INTO #tmp
FROM cte c
  CROSS APPLY c.tableXML.nodes('//R/child::node()') rowset(c)
     CROSS APPLY c.tableXML.nodes('//CDEF/child::node()') ColumnSet(c)


• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  January 19, 2018 at 12:48 am

  #1976454

  Quick thought, you can extract the data in an EAV format and then pivot it, here is a query for the EAV
  😎

  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.XMLwithOpenXML') IS NOT NULL DROP TABLE dbo.XMLwithOpenXML;
CREATE TABLE dbo.XMLwithOpenXML ( rawXMLId INT IDENTITY(1,1) , XMLData XML ,TxtData VARCHAR(max) )

  INSERT INTO dbo.XMLwithOpenXML (XMLData)
SELECT '<APPMANAGER>
<DATADETAIL>
<TABLE NAME="Table1" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table1 Discription">
<EXTENDEDNAME>Table1 Desc Report</EXTENDEDNAME>
<CDEF>
 <C0 NAME="Table1 Col1" TYPE="String" />
 <C1 NAME="Table1 Col2" TYPE="String" />
 <C2 NAME="Table1 Col3" TYPE="String" />
</CDEF>
<R>
 <C0 DataType="RawData">Table1 l11</C0>
 <C1 DataType="RawData">Table1 l12</C1>
 <C2 DataType="RawData">Table1 l13</C2>
</R>
<R>
 <C0 DataType="RawData">Table1 l21</C0>
 <C1 DataType="RawData">Table1 l22</C1>
 <C2 DataType="RawData">Table1 l23</C2>
</R>
</TABLE>
<TABLE NAME="Table2" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table2 Desc">
<EXTENDEDNAME>Table2 Desc Report</EXTENDEDNAME>
<CDEF>
 <C0 NAME="Table2 Col1" TYPE="String" />
 <C1 NAME="Table3 Col2" TYPE="String" />
 <C2 NAME="Table3 Col3" TYPE="String" />
</CDEF>
<R>
 <C0 DataType="RawData">Table2 l11</C0>
 <C1 DataType="RawData">Table2 l12</C1>
 <C2 DataType="RawData">Table2 l13</C2>
</R>
</TABLE>
</DATADETAIL>
</APPMANAGER>'
UNION
SELECT '
<APPMANAGER>
<DATADETAIL>
<TABLE NAME="Table1" UUID="{65DB28B7-D879-4caa-B89A-839D10C9BF5B}" DESCRIPTION="Table1 Discription">
<EXTENDEDNAME>Table1 Desc Report</EXTENDEDNAME>
<CDEF>
 <C0 NAME="Table1 Col1" TYPE="String" />
 <C1 NAME="Table1 Col2" TYPE="String" />
 <C2 NAME="Table1 Col3" TYPE="String" />
</CDEF>
<R>
 <C0 DataType="RawData">Table1 l11</C0>
 <C1 DataType="RawData">Table1 l12</C1>
 <C2 DataType="RawData">Table1 l13</C2>
</R>
<R>
 <C0 DataType="RawData">Table1 l21</C0>
 <C1 DataType="RawData">Table1 l22</C1>
 <C2 DataType="RawData">Table1 l23</C2>
</R>
</TABLE>
</DATADETAIL>
</APPMANAGER>';
;WITH BASE_DATA AS
(
SELECT
  XX.rawXMLId
 ,CDEF.DATA.query('.') AS ROW_XML 
 ,ROW_NUMBER() OVER (PARTITION BY CDEF.DATA.value('../@NAME','VARCHAR(50)') ORDER BY @@VERSION) AS ROW_NO
 ,CDEF.DATA.value('../@NAME','VARCHAR(50)') AS TABLE_NAME
FROM   dbo.XMLwithOpenXML XX
CROSS APPLY XX.XMLData.nodes('/APPMANAGER/DATADETAIL/TABLE/R') CDEF(DATA)
)
SELECT
  BD.rawXMLId            AS XML_ID
 ,BD.TABLE_NAME           AS TABLE_NAME
 ,BD.ROW_NO             AS ROW_NO
 ,COL.DATA.value('local-name(.)','VARCHAR(50)') AS COL_NAME
 ,COL.DATA.value('(./text())[1]','VARCHAR(50)') AS COL_VALUE
FROM   BASE_DATA BD
CROSS APPLY BD.ROW_XML.nodes('/R/*') COL(DATA)
;

  Output
  XML_ID TABLE_NAME ROW_NO COL_NAME COL_VALUE
------- ----------- ------- --------- -----------
1   Table1  1   C0   Table1 l11
1   Table1  1   C1   Table1 l12
1   Table1  1   C2   Table1 l13
1   Table1  2   C0   Table1 l21
1   Table1  2   C1   Table1 l22
1   Table1  2   C2   Table1 l23
2   Table1  3   C0   Table1 l11
2   Table1  3   C1   Table1 l12
2   Table1  3   C2   Table1 l13
2   Table1  4   C0   Table1 l21
2   Table1  4   C1   Table1 l22
2   Table1  4   C2   Table1 l23
1   Table2  1   C0   Table2 l11
1   Table2  1   C1   Table2 l12
1   Table2  1   C2   Table2 l13


• Bala'

  Ten Centuries

  Points: 1186

  More actions

  January 19, 2018 at 2:40 am

  #1976461

  Great thanks ..

  ColumnName , need to extract the names  For  NAME="Table2 Col1" not the local-name(.)'
  <C0 NAME="Table1 Col1" TYPE="String" />
  <C1 NAME="Table1 Col2" TYPE="String" />
  <C2 NAME="Table1 Col3" TYPE="String" />

  Basically need to  Replace the column name C0 - Table1 Col1 , C1 - Table1 Col2" C3 - Table1 Col3"

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  January 19, 2018 at 4:07 am

  #1976466

  Bala' - Friday, January 19, 2018 2:40 AM

  Great thanks ..

  ColumnName , need to extract the names  For  NAME="Table2 Col1" not the local-name(.)'
  <C0 NAME="Table1 Col1" TYPE="String" />
  <C1 NAME="Table1 Col2" TYPE="String" />
  <C2 NAME="Table1 Col3" TYPE="String" />

  Basically need to  Replace the column name C0 - Table1 Col1 , C1 - Table1 Col2" C3 - Table1 Col3"

  The local-name(.) function is returning the column element name from the XML, the table name attribute is already in the result set as 

  TABLE_NAME
  

  CDEF.DATA.value('../@NAME','VARCHAR(50)') AS TABLE_NAME

  😎