Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Распарсить книгу xml 2003  [new]
assmsk
Member

Откуда:
Сообщений: 173
Всем привет, есть книга excel в формате xml 2003.
Не получается ее распарсить с помощью xml.nodes

Структура 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">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>xxx</Author>
  <LastAuthor>xxxxxxxx</LastAuthor>
  <Created>2019-07-26T14:18:48Z</Created>
  <Version>16.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8700</WindowHeight>
  <WindowWidth>24000</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Font ss:FontName="Arial" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s63">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Arial" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s67">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Arial" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <NumberFormat ss:Format="@"/>
  </Style>
  <Style ss:ID="s70">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Arial" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <NumberFormat ss:Format="#,##0"/>
  </Style>
  <Style ss:ID="s73">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Arial" x:CharSet="204" x:Family="Swiss" ss:Size="11"
    ss:Color="#000000"/>
   <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
   <NumberFormat ss:Format="@"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="541" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="14.25">
   <Column ss:StyleID="s62" ss:Width="86.25"/>
   <Column ss:StyleID="s62" ss:Width="73.5" ss:Span="1"/>
   <Column ss:Index="4" ss:StyleID="s62" ss:Width="200.25" ss:Span="537"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:MergeAcross="2" ss:StyleID="s67"/>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">ФИО</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">Грейд</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">Ранг</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s70"><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="s70"><Data ss:Type="Number">2</Data></Cell>
    <Cell ss:StyleID="s70"><Data ss:Type="Number">3</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]Никулина О.И[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]7[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]1[/color][/color]</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]Николаева А.В[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]6[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]2[/color][/color]</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]Сидорова Н.П[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]9[/color][/color]</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">[color=red][color=red]6[/color][/color]</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>9</ActiveRow>
     <ActiveCol>4</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="541" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="14.25">
   <Column ss:StyleID="s62" ss:Width="86.25"/>
   <Column ss:StyleID="s62" ss:Width="73.5" ss:Span="1"/>
   <Column ss:Index="4" ss:StyleID="s62" ss:Width="200.25" ss:Span="537"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:MergeAcross="2" ss:StyleID="s73"/>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">ФИО</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">Грейд</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">Ранг</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s70"><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="s70"><Data ss:Type="Number">2</Data></Cell>
    <Cell ss:StyleID="s70"><Data ss:Type="Number">3</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">Иванов И.И</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">5</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">2</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">Петров А.А</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">3</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">4</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s63"><Data ss:Type="String">Сидоров О.О</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">1</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String">6</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Zoom>70</Zoom>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>3</ActiveRow>
     <ActiveCol>1</ActiveCol>
     <RangeSelection>R4C2:R6C3</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>



На выходе я хочу получить 2 таблицы одна с листа 1, вторая с листа 2
Делаю

DECLARE @X XML = (SELECT *  FROM OPENROWSET (BULK 'С:\TEST.XML', SINGLE_CLOB)   t)


А дальше загвоздка с путями в методе nodes, как ни пишу получаю пустоту.

Как написать запрос типа :
SELECT T.c.value('???','VARCHAR(50)') as fio,
T.c.value('???','INT') as graid,
T.c.value('???','INT') as rang
FROM @X.nodes('????') as t (c)


чтобы получить таблицу вида:

ФИО Грейд Ранг
Иванов И.И 5 2
Петров А.А 3 4
Сидоров О.О 1 6

Сообщение было отредактировано: 29 июл 19, 15:27
29 июл 19, 15:09    [21936915]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
assmsk,

Это специально так гланды удаляете? Или есть причины такого расположения гланд
29 июл 19, 15:19    [21936924]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
assmsk
Member

Откуда:
Сообщений: 173
TaPaK, в xml ноль поэтому не понимаю вопрос)
29 июл 19, 15:27    [21936931]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
assmsk
TaPaK, в xml ноль поэтому не понимаю вопрос)

sql умеет и прям из excel
29 июл 19, 15:50    [21936952]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
assmsk
Member

Откуда:
Сообщений: 173
TaPaK,
раньше файл загружался через openquery эксельным драйвером.
Но сейчас стоит задача именно его парсить как xml
29 июл 19, 15:54    [21936956]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
assmsk
TaPaK,
раньше файл загружался через openquery эксельным драйвером.
Но сейчас стоит задача именно его парсить как xml


Вы учитывайте, что для XML то, что какая-то строка в Excel является заголовком - абсолютно пофиг. Он работает с нодами, атрибутами и значениями.

Если понимаете, как с этим дальше жить, то вот вам, дальше копайте:

WITH xmlnamespaces('urn:schemas-microsoft-com:office:spreadsheet' AS ss,
                    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet')
SELECT  r.v.value('(Cell[1]/Data/text())[1]', 'nvarchar(max)') AS col1,
        r.v.value('(Cell[2]/Data/text())[1]', 'nvarchar(max)') AS col2,
        r.v.value('(Cell[2]/Data/text())[1]', 'nvarchar(max)') AS col2
FROM @xml.nodes('//Worksheet[@ss:Name="Sheet2"]/Table/Row') AS r(v)
29 июл 19, 17:25    [21937069]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
...


WITH xmlnamespaces('urn:schemas-microsoft-com:office:spreadsheet' AS ss,
                    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet')
SELECT  r.v.value('(Cell[1]/Data/text())[1]', 'nvarchar(max)') AS col1,
        r.v.value('(Cell[2]/Data/text())[1]', 'nvarchar(max)') AS col2,
        r.v.value('(Cell[3]/Data/text())[1]', 'nvarchar(max)') AS col2
FROM @xml.nodes('//Worksheet[@ss:Name="Sheet2"]/Table/Row') AS r(v)


Ну так, конечно же, опечаточка...
29 июл 19, 17:26    [21937072]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
assmsk
Member

Откуда:
Сообщений: 173
Minamoto,
А в данном способе можно как то поменять кодировку, текст вот в таком виде грузится - РЎРёРґРѕСЂРѕРІ Рћ.Рћ
29 июл 19, 17:54    [21937128]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
assmsk, тут я, к сожалению, не подскажу. Была такая проблема, решил, наугад перебирая параметры OPENROWSET (SINGLE_BLOB | SINGLE_NCLOB вместо SINGLE_CLOB) и зачитывая в nvarchar, прежде чем конвертировать в XML.

Ну и, если есть возможность, можно поиграться с тем, как сам файл сохраняется.
29 июл 19, 18:05    [21937144]     Ответить | Цитировать Сообщить модератору
 Re: Распарсить книгу xml 2003  [new]
assmsk
Member

Откуда:
Сообщений: 173
Minamoto, спасибо.
Заработало с SINGLE_BLOB
29 июл 19, 18:27    [21937170]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить