aaa级片,91精品国产色综合久久不卡蜜臀,国产精品免费在线播放

本文介紹了將具有相同標記的 XML 值分成不同的行 SQL Server的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

限時送ChatGPT賬號..

我有一個要解析的 XML 文件.XML 是使用

I have an XML File which I am trying to parse. The XML was created through Excel using

另存為 XML

因為 XML 文件是從 Microsoft Excel 創建的，所以它有這個標題:

Because the XML file was created from Microsoft Excel, it has this header:

<?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">

我試圖提取的數據是這樣設置的:

The data I am trying to extract is set up in blocks like this:

<Row ss:AutoFitHeight="0" ss:Height="30">
    <Cell ss:StyleID="s22"/>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Jane Doe</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">JaneDoe</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">XYZ</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">(555) 555-5555</Data></Cell>
    <Cell ss:StyleID="s22"/>
   </Row>

現在，我的查詢如下所示:

Right now, my query looks like this:

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss)

select * from (
select X.value('local-name(.)[1]','varchar(max)') as Name,
X.value('.[1]','varchar(max)') as Value
from @allUsers.nodes('//*') as T(X)
    ) a
where Name  = 'Data'

并給我這些結果:

Name    Value
----    -----------
Data    Jane Doe
Data    JaneDoe
Data    XYZ
Data    (555)555-5555

我想做的是將它分成 4 行，所以我有類似的東西:

What I would like to do is separate this into 4 rows, so I have something like:

Name      UserName    Address    Phone
-----     ----------  ---------  ----------
Jane Doe  JaneDoe     XYZ        (555)-555-5555

我嘗試選擇一列作為

X.value('.[2]','varchar(max)') as UserName

但我只是得到了所有的 NULL 值.

but I just get all NULL values for that.

有沒有辦法做到這一點?

Is there any way to do this?

XML 文件的一般結構如下:

The general structure of the XML file looks like:

<Workbook>
  <DocumentProperties>
  </DocumentProperties>
  <ExcelWorkbook>
  </ExcelWorkbook>
  <Styles>
    <Style>
    </Style>
  </Styles>
  <Worksheet>
    <Table>
      <Column.../>
      <Column.../>
      <Column.../>
      <Row>
        <Cell.../>
        <Cell><Data>...</Data></Cell>
        <Cell><Data>...</Data></Cell>
        <Cell><Data>...</Data></Cell>
        <Cell><Data>...</Data></Cell>
        <Cell.../>
      </Row>
      ...
    </Table>
  </Worksheet>

我想要獲取的信息在 ...</Data> 字段

and the information I am trying to get is in the <Data>...</Data> field

編輯

從我表述這個問題的方式來看，標題名稱似乎已經被編入，但它們實際上被讀取為 <;/Cell>.我也不確定這部分的用途是什么

From the way I phrased the question, it would seem like the header names are already programmed in, but they are actually read as rows in <Cell><Data><Data/></Cell>. I am also not sure what purpose the part serves

這是部分的開始:

<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="2685" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="26.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="117" ss:Span="3"/>
   <Column ss:Index="6" ss:AutoFitWidth="0" ss:Width="29.25"/>
   <Row ss:AutoFitHeight="0" ss:Height="60"> --Contains the header names
    <Cell ss:StyleID="s22"/>
    <Cell ss:StyleID="s23"><Data ss:Type="String">Name</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="String">UserName</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="String">Address</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="String">Telephone Number</Data></Cell>
    <Cell ss:StyleID="s22"/>
   </Row>

   <Row ss:AutoFitHeight="0" ss:Height="30"> --First record I would like to extract
    <Cell ss:StyleID="s22"/>
    <Cell ss:StyleID="s24"><Data ss:Type="String">John Smith</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">JSmith</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">ABC</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">(999) 999-9999</Data></Cell>
    <Cell ss:StyleID="s22"/>
   </Row>

推薦答案

同一用戶提出了兩個非常相似的問題.OP 決定刪除一個并在此處合并，并要求我將我的答案從那里復制到此線程.

There were two very similar question by the same user. The OP decided to delete one and combine this here and asked me to copy my answer from there to this thread.

注意必須聲明為DEFAULT"的 xmlns-namespace:

Be aware of the xmlns-namespace which must be declared as "DEFAULT":

簡化了您的 XML，但這個想法應該沒問題...

Simplified your XML, but the idea should be OK...

DECLARE @allUsers 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">
 <Worksheet>
 <Table>
   <Row ss:AutoFitHeight="0" ss:Height="30">
    <Cell ss:StyleID="s22"/>
    <Cell ss:StyleID="s24"><Data ss:Type="String">Jane Doe</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">JaneDoe</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">XYZ</Data></Cell>
    <Cell ss:StyleID="s24"><Data ss:Type="String">(555) 555-5555</Data></Cell>
    <Cell ss:StyleID="s22"/>
   </Row>
   </Table>
 </Worksheet>   
</Workbook>';

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss
                     ,DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet')
SELECT T.X.value('Cell[1]/Data[1]','varchar(max)') AS DontKnow1
      ,T.X.value('Cell[2]/Data[1]','varchar(max)') AS Name
      ,T.X.value('Cell[3]/Data[1]','varchar(max)') AS UserName
      ,T.X.value('Cell[4]/Data[1]','varchar(max)') AS DontKnow2
      ,T.X.value('Cell[5]/Data[1]','varchar(max)') AS Telephone
      ,T.X.value('Cell[6]/Data[1]','varchar(max)') AS DontKnow3
FROM @allUsers.nodes('/Workbook/Worksheet/Table/Row') as T(X)

這篇關于將具有相同標記的 XML 值分成不同的行 SQL Server的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

將具有相同標記的 XML 值分成不同的行 SQL Server

問題描述

推薦答案

相關文檔推薦