本文介紹了如何在 SQL Server 2008 中讀取 XML 列?的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
我從未在 SQL Server 2008 中使用過 XML，我需要將一個字段列表提取到一個變量表中，你怎么做?
I have never used XML in SQL Server 2008, I need to extract a list of fields into a variable table how do you do it?
鑒于我在 XMLMain 表中有一個名為 xmldata 的列，它看起來像下面這樣，如何提取 sql 中的字段列表?
Given that I have a column called xmldata in a XMLMain table that looks like something like below how do I extract the list of fields in sql?
![在此處輸入圖片說明][1]
![enter image description here][1]
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:SampleForm:-myXSD-2014-03-29T09-41-23" solutionVersion="1.0.0.18" productVersion="15.0.0.0" PIVersion="1.0.0.0" href="http://bipc20/sites/team-1303/FormServerTemplates/SampleForm.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.4"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
<my:field1>1</my:field1>
<my:field2>2</my:field2>
<my:field3>true</my:field3>
<my:field4 xsi:nil="true" />
<my:field5 xsi:nil="true" />
<my:field6>4</my:field6>
<my:FormName>2014-04-01T15:11:47</my:FormName>
<my:Repeating>hi</my:Repeating>
<my:Repeating xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23">hello</my:Repeating>
<my:Repeating xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23">how are you?</my:Repeating>
</my:myFields>
我想以逗號分隔的形式提取重復字段的值，例如 ex.在重復中，我們有三個值(你好，你好嗎?)
I want to extract value of Repeating field as comma separated like for ex. in Repeating we have three values (hello,how are you?)
有人可以幫我嗎?
推薦答案
with xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23' as my)
select M.XMLData.value('(/my:myFields/my:field1/text())[1]', 'int') as field1,
M.XMLData.value('(/my:myFields/my:field2/text())[1]', 'int') as field2,
M.XMLData.value('(/my:myFields/my:field3/text())[1]', 'bit') as field3,
M.XMLData.value('(/my:myFields/my:FormName/text())[1]', 'datetime') as FormName,
(
select ','+R.X.value('text()[1]', 'nvarchar(max)')
from M.XMLData.nodes('/my:myFields/my:Repeating') as R(X)
for xml path(''), type
).value('substring(text()[1], 2)', 'nvarchar(max)') as Repeating
from XMLMain as M
結果:
field1 field2 field3 FormName Repeating
----------- ----------- ------ ----------------------- -----------------------
1 2 1 2014-04-01 15:11:47.000 hi,hello,how are you?
這篇關于如何在 SQL Server 2008 中讀取 XML 列?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！