Shredding of XML

10. července 2012 11:11

0

DECLARE @x xml;

SET @x = '

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal" />
<Style ss:ID="Total" ss:Name="Total">
<Font ss:Bold="1" />
</Style>
<Style ss:ID="Date" ss:Name="Date">
<NumberFormat ss:Format="MM/dd/yyyy" />
</Style>
<Style ss:ID="Date_Total" ss:Name="Date_Total">
<NumberFormat ss:Format="MM/dd/yyyy" />
<Font ss:Bold="1" />
</Style>
<Style ss:ID="Number" ss:Name="Number">
<NumberFormat ss:Format="#,##0.00;\-#,##0.00" />
</Style>
<Style ss:ID="Number_Total" ss:Name="Number_Total">
<NumberFormat ss:Format="#,##0.00;\-#,##0.00" />
<Font ss:Bold="1" />
</Style>
<Style ss:ID="Number_Percent" ss:Name="Number_Percent">
<NumberFormat ss:Format="#,##0.00%;\-#,##0.00%" />
</Style>
<Style ss:ID="Number_Total_Percent" ss:Name="Number_Total_Percent">
<NumberFormat ss:Format="#,##0.00%;\-#,##0.00%" />
<Font ss:Bold="1" />
</Style>
</Styles>
<Worksheet ss:Name="Report">
<Table>
<Row>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Entry Date</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">User Name</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Client Name</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Project Name</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Billable Hrs</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Non-Billable Hrs</Data>
</Cell>
</Row>
<Row>
<Cell ss:StyleID="Date">
<Data ss:Type="DateTime">2012-07-02T00:00:00</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Adusumilli, Satyapawan</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">WallMart</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">WebMethods Integration Developer - WallMart</Data>
</Cell>
<Cell ss:StyleID="Number">
<Data ss:Type="Number">9.00</Data>
</Cell>
<Cell ss:StyleID="Number">
<Data ss:Type="Number">0.00</Data>
</Cell>
</Row>
<Row>
<Cell ss:StyleID="Date">
<Data ss:Type="DateTime">2012-07-07T00:00:00</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">Tallman, Matt</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">WallMart</Data>
</Cell>
<Cell ss:StyleID="Default">
<Data ss:Type="String">TLC Banner Site Development - WallMart</Data>
</Cell>
<Cell ss:StyleID="Number">
<Data ss:Type="Number">0.00</Data>
</Cell>
<Cell ss:StyleID="Number">
<Data ss:Type="Number">0.00</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>';

How to shred this xml into sql table.

Thanks
- Reagovat
- Citovat

Všechny reakce

10. července 2012 11:47

Přispěvatel

How does your target table look? Here's a few examples:

DECLARE @xml xml;
 
SET @xml = '<?xml version="1.0"?>
 <?mso-application progid="Excel.Sheet"?>
 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
   <Styles>
     <Style ss:ID="Default" ss:Name="Normal" />
     <Style ss:ID="Total" ss:Name="Total">
       <Font ss:Bold="1" />
     </Style>
     <Style ss:ID="Date" ss:Name="Date">
       <NumberFormat ss:Format="MM/dd/yyyy" />
     </Style>
     <Style ss:ID="Date_Total" ss:Name="Date_Total">
       <NumberFormat ss:Format="MM/dd/yyyy" />
       <Font ss:Bold="1" />
     </Style>
     <Style ss:ID="Number" ss:Name="Number">
       <NumberFormat ss:Format="#,##0.00;\-#,##0.00" />
     </Style>
     <Style ss:ID="Number_Total" ss:Name="Number_Total">
       <NumberFormat ss:Format="#,##0.00;\-#,##0.00" />
       <Font ss:Bold="1" />
     </Style>
     <Style ss:ID="Number_Percent" ss:Name="Number_Percent">
       <NumberFormat ss:Format="#,##0.00%;\-#,##0.00%" />
     </Style>
     <Style ss:ID="Number_Total_Percent" ss:Name="Number_Total_Percent">
       <NumberFormat ss:Format="#,##0.00%;\-#,##0.00%" />
       <Font ss:Bold="1" />
     </Style>
   </Styles>
   <Worksheet ss:Name="Report">
     <Table>
       <Row>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Entry Date</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">User Name</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Client Name</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Project Name</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Billable Hrs</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Non-Billable Hrs</Data>
         </Cell>
       </Row>
      <Row>
         <Cell ss:StyleID="Date">
           <Data ss:Type="DateTime">2012-07-02T00:00:00</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Adusumilli, Satyapawan</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">WallMart</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">WebMethods Integration Developer - WallMart</Data>
         </Cell>
         <Cell ss:StyleID="Number">
           <Data ss:Type="Number">9.00</Data>
         </Cell>
         <Cell ss:StyleID="Number">
           <Data ss:Type="Number">0.00</Data>
         </Cell>
       </Row>
       <Row>
         <Cell ss:StyleID="Date">
           <Data ss:Type="DateTime">2012-07-07T00:00:00</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">Tallman, Matt</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">WallMart</Data>
         </Cell>
         <Cell ss:StyleID="Default">
           <Data ss:Type="String">TLC Banner Site Development - WallMart</Data>
         </Cell>
         <Cell ss:StyleID="Number">
           <Data ss:Type="Number">0.00</Data>
         </Cell>
         <Cell ss:StyleID="Number">
           <Data ss:Type="Number">0.00</Data>
         </Cell>
       </Row>
     </Table>
   </Worksheet>
 </Workbook>';

--select @xml

;WITH XMLNAMESPACES ( DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
 )
 --INSERT INTO ...
SELECT
	r.rowId,
	c.c.value('@ss:StyleID', 'VARCHAR(MAX)') StyleID,
	d.c.value('@ss:Type', 'VARCHAR(MAX)') [Type],
	d.c.value('.', 'VARCHAR(MAX)') [Value]
FROM
	(
	SELECT
		ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rowId,
		r.c.query('.') data
	FROM @xml.nodes('Workbook/Worksheet/Table/Row') r(c)
	) r
	CROSS APPLY r.data.nodes('Row/ss:Cell') c(c)
		CROSS APPLY c.c.nodes('ss:Data') d(c)
  

;WITH XMLNAMESPACES ( DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
)
SELECT 
	r.c.value('Cell[1]/@ss:StyleID', 'VARCHAR(MAX)') Cell1_Style,
	r.c.value('(Cell[1]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell1_Type,
	r.c.value('(Cell[1]/Data)[1]', 'VARCHAR(MAX)') Cell1_Value,
	r.c.value('Cell[2]/@ss:StyleID', 'VARCHAR(MAX)') Cell2_Style,
	r.c.value('(Cell[2]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell2_Type,
	r.c.value('(Cell[2]/Data)[1]', 'VARCHAR(MAX)') Cell2_Value,
	r.c.value('Cell[3]/@ss:StyleID', 'VARCHAR(MAX)') Cell3_Style,
	r.c.value('(Cell[3]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell3_Type,
	r.c.value('(Cell[3]/Data)[1]', 'VARCHAR(MAX)') Cell3_Value,
	r.c.value('Cell[4]/@ss:StyleID', 'VARCHAR(MAX)') Cell4_Style,
	r.c.value('(Cell[4]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell4_Type,
	r.c.value('(Cell[4]/Data)[1]', 'VARCHAR(MAX)') Cell4_Value,
	r.c.value('Cell[5]/@ss:StyleID', 'VARCHAR(MAX)') Cell5_Style,
	r.c.value('(Cell[5]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell5_Type,
	r.c.value('(Cell[5]/Data)[1]', 'VARCHAR(MAX)') Cell5_Value,
	r.c.value('Cell[6]/@ss:StyleID', 'VARCHAR(MAX)') Cell6_Style,
	r.c.value('(Cell[6]/Data/@ss:Type)[1]', 'VARCHAR(MAX)') Cell6_Type,
	r.c.value('(Cell[6]/Data)[1]', 'VARCHAR(MAX)') Cell6_Value

FROM @xml.nodes('Workbook/Worksheet/Table/Row') r(c)

;WITH XMLNAMESPACES ( DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
)
SELECT 
	r.c.value('(Cell[1]/Data)[1]', 'VARCHAR(MAX)') Cell1_Value,
	r.c.value('(Cell[2]/Data)[1]', 'VARCHAR(MAX)') Cell2_Value,
	r.c.value('(Cell[3]/Data)[1]', 'VARCHAR(MAX)') Cell3_Value,
	r.c.value('(Cell[4]/Data)[1]', 'VARCHAR(MAX)') Cell4_Value,
	r.c.value('(Cell[5]/Data)[1]', 'VARCHAR(MAX)') Cell5_Value,
	r.c.value('(Cell[6]/Data)[1]', 'VARCHAR(MAX)') Cell6_Value

FROM @xml.nodes('Workbook/Worksheet/Table/Row') r(c)

10. července 2012 16:22

Hi,

My target table should look like this,

Entry Date	User Name	Client Name	Project Name	Billable Hrs	Non-Billable Hrs
7/2/2012	Adusumilli, Satyapawan	WallMart	WebMethods Integration Developer - WallMart	9	0
7/7/2012	Tallman, Matt	WallMart	TLC Banner Site Development - WallMart	0	0

Thanks

Upravený khushi_10 10. července 2012 16:33
Upravený khushi_10 10. července 2012 16:50

10. července 2012 16:53

Přispěvatel

So you can use my second query, slightly adapted, eg

;WITH XMLNAMESPACES ( DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
'urn:schemas-microsoft-com:office:spreadsheet' AS ss
)
--INSERT INTO yourTargetTable ...
SELECT 
	r.c.value('(Cell[1]/Data)[1]', 'VARCHAR(MAX)') Cell1_Value,
	r.c.value('(Cell[2]/Data)[1]', 'VARCHAR(MAX)') Cell2_Value,
	r.c.value('(Cell[3]/Data)[1]', 'VARCHAR(MAX)') Cell3_Value,
	r.c.value('(Cell[4]/Data)[1]', 'VARCHAR(MAX)') Cell4_Value,
	r.c.value('(Cell[5]/Data)[1]', 'VARCHAR(MAX)') Cell5_Value,
	r.c.value('(Cell[6]/Data)[1]', 'VARCHAR(MAX)') Cell6_Value

FROM @xml.nodes('Workbook/Worksheet/Table/Row[position()> 1 ]') r(c)

Navržen jako odpověď wBobMicrosoft Community Contributor, Editor 10. července 2012 16:53
Označen jako odpověď khushi_10 11. července 2012 4:00

11. července 2012 4:00

0

Thnaks wBob
- Reagovat
- Citovat