台灣最大程式設計社群網站
線上人數
1377
 
會員總數:244807
討論主題:188842
歡迎您免費加入會員
討論區列表 >> MS SQL >> XML轉SQL Table 方式
[]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
XML轉SQL Table 方式
價值 : 5 QP  點閱數:856 回應數:1

樓主

Gucci0925
門外漢
0 1
26 0
發送站內信

<Form formVersionId="9c3b919c-1ab3-462a-95e2-646fed4d2ccc">
<FormFieldValue>
<FieldItem fieldId="Manual" fieldValue="表單填寫說明" realValue="" fillerName="周小姐" fillerUserGuid="554974b9-1295-42d4-b5b2-f8dd1ec9db7c" fillerAccount="88600XXX" fillSiteId="" />
<FieldItem fieldId="DocNbr" fieldValue="HRD-5180700001" realValue="" />
<FieldItem fieldId="Applicant" fieldValue="李XX LEE Justin(88600YYY)" realValue="&lt;UserSet&gt;&lt;Element type='user'&gt; &lt;userId&gt;440d57c7-9774-463e-9a2a-dd7ecd2c2b5b&lt;/userId&gt;&lt;/Element&gt;&lt;/UserSet&gt; " />
<FieldItem fieldId="Department" fieldValue="TAIPEI PROJECTS" realValue="0a6bf965-ab95-7b68-4992-eee9314a8fe6,TAIPEI PROJECTS,False" />
<FieldItem fieldId="ProjectRef" fieldValue="案名案號參考表" realValue="" fillerName="周小姐" fillerUserGuid="554974b9-1295-42d4-b5b2-f8dd1ec9db7c" fillerAccount="88600XXX" fillSiteId="" />
<FieldItem fieldId="PMApprove" fieldValue="需要PM簽核" realValue="" customValue="@null" fillerName="周小姐" fillerUserGuid="554974b9-1295-42d4-b5b2-f8dd1ec9db7c" fillerAccount="88600XXX" fillSiteId="" />
<FieldItem fieldId="FeeDetail" fillerName="周小姐" fillerUserGuid="554974b9-1295-42d4-b5b2-f8dd1ec9db7c" fillerAccount="88600XXX" fillSiteId="">
<DataGrid>
<Row order="0">
<Cell fieldId="Date" fieldValue="2018/06/13" realValue="" customValue="" />
<Cell fieldId="ProjectCode" fieldValue="40/001/07-005" realValue="" customValue="" />
<Cell fieldId="ProjectName" fieldValue="STE/TTYMRT" realValue="" customValue="" />
<Cell fieldId="SubjectCode" fieldValue="0007D" realValue="" customValue="" />
<Cell fieldId="From" fieldValue="台北" realValue="" customValue="" />
<Cell fieldId="Destination" fieldValue="青埔機廠" realValue="" customValue="" />
<Cell fieldId="Particulars" fieldValue="專案支援" realValue="" customValue="" />
<Cell fieldId="Km" fieldValue="43" realValue="" customValue="" />
<Cell fieldId="FeePerKm" fieldValue="6" realValue="" customValue="" />
<Cell fieldId="TransportClaim" fieldValue="258" realValue="" customValue="" />
<Cell fieldId="HiwayToll" fieldValue="33" realValue="" customValue="" />
<Cell fieldId="Parking" fieldValue="0" realValue="" customValue="" />
</Row>
<Row order="1">
<Cell fieldId="Date" fieldValue="2018/06/13" realValue="" customValue="" />
<Cell fieldId="ProjectCode" fieldValue="40/001/07-005" realValue="" customValue="" />
<Cell fieldId="ProjectName" fieldValue="STE/TTYMRT" realValue="" customValue="" />
<Cell fieldId="SubjectCode" fieldValue="0007D" realValue="" customValue="" />
<Cell fieldId="From" fieldValue="青埔機廠" realValue="" customValue="" />
<Cell fieldId="Destination" fieldValue="台北" realValue="" customValue="" />
<Cell fieldId="Particulars" fieldValue="回程" realValue="" customValue="" />
<Cell fieldId="Km" fieldValue="43" realValue="" customValue="" />
<Cell fieldId="FeePerKm" fieldValue="6" realValue="" customValue="" />
<Cell fieldId="TransportClaim" fieldValue="258" realValue="" customValue="" />
<Cell fieldId="HiwayToll" fieldValue="33" realValue="" customValue="" />
<Cell fieldId="Parking" fieldValue="0" realValue="" customValue="" />
</Row>
<Row order="2">
<Cell fieldId="Date" fieldValue="2018/06/14" realValue="" customValue="" />
<Cell fieldId="ProjectCode" fieldValue="40/001/07-005" realValue="" customValue="" />
<Cell fieldId="ProjectName" fieldValue="STE/TTYMRT" realValue="" customValue="" />
<Cell fieldId="SubjectCode" fieldValue="0007D" realValue="" customValue="" />
<Cell fieldId="From" fieldValue="台北" realValue="" customValue="" />
<Cell fieldId="Destination" fieldValue="青埔機廠" realValue="" customValue="" />
<Cell fieldId="Particulars" fieldValue="專案支援" realValue="" customValue="" />
<Cell fieldId="Km" fieldValue="43" realValue="" customValue="" />
<Cell fieldId="FeePerKm" fieldValue="6" realValue="" customValue="" />
<Cell fieldId="TransportClaim" fieldValue="258" realValue="" customValue="" />
<Cell fieldId="HiwayToll" fieldValue="33" realValue="" customValue="" />
<Cell fieldId="Parking" fieldValue="0" realValue="" customValue="" />
</Row>
<Row order="3">
<Cell fieldId="Date" fieldValue="2018/06/14" realValue="" customValue="" />
<Cell fieldId="ProjectCode" fieldValue="40/001/07-005" realValue="" customValue="" />
<Cell fieldId="ProjectName" fieldValue="STE/TTYMRT" realValue="" customValue="" />
<Cell fieldId="SubjectCode" fieldValue="0007D" realValue="" customValue="" />
<Cell fieldId="From" fieldValue="青埔機廠" realValue="" customValue="" />
<Cell fieldId="Destination" fieldValue="台北" realValue="" customValue="" />
<Cell fieldId="Particulars" fieldValue="回程" realValue="" customValue="" />
<Cell fieldId="Km" fieldValue="43" realValue="" customValue="" />
<Cell fieldId="FeePerKm" fieldValue="6" realValue="" customValue="" />
<Cell fieldId="TransportClaim" fieldValue="258" realValue="" customValue="" />
<Cell fieldId="HiwayToll" fieldValue="33" realValue="" customValue="" />
<Cell fieldId="Parking" fieldValue="0" realValue="" customValue="" />
</Row>
</DataGrid>
</FieldItem>
<FieldItem fieldId="ClainSubtotal" fieldValue="1032" realValue="" />
<FieldItem fieldId="TollSubtotal" fieldValue="132" realValue="" />
<FieldItem fieldId="ParkSubtotal" fieldValue="0" realValue="" />
<FieldItem fieldId="Total" fieldValue="1164" realValue="" />
<FieldItem fieldId="FileUpload" fieldValue="" realValue="" />
<FieldItem fieldId="Remark" fieldValue="TESTING" realValue="" fillerName="周小姐" fillerUserGuid="554974b9-1295-42d4-b5b2-f8dd1ec9db7c" fillerAccount="88600XXX" fillSiteId="" />
<FieldItem fieldId="Remark2" fieldValue="備註" realValue="" />
</FormFieldValue>
</Form>

如上面那一段XML, 想把<Row> element以下<Cell> 資料轉成 Table 像欄位顯示方式, 請問大大 SQL 指令應該如何下??
Date ProjectCode ProjectName SubjectCode From Destination Particulars ......

搜尋相關Tags的文章: [ XML ] , [ SQL ] ,
本篇文章發表於2018-07-16 10:35
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
1樓
回應

Jimmy Chen
檢舉此回應
可參考 xml nodes()

大概的作法如下:
// 先定義 xml 變數,並把內容塞入
DECLARE @xml xml
SET @xml = 'xml content'

// 用 nodes() 剖析 XML
SELECT
[定義欄位1]
,[定義欄位2]
FROM @xml.nodes('[xml-tag1]/[xml-tag2]/[xml-tag]/[xml-field]')
本篇文章回覆於2018-08-22 10:05
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
   
1

回覆
如要回應,請先登入.