SQL Server 查询元素值的 xml 属性 [英] SQL Server query xml attribute for an element value
问题描述
对不起,如果这是在其他地方,我发现了很多类似的例子,但我一直无法使用我的数据.2 天后,我需要一个答案:(
Sorry if this is somewhere else, I have found a lot of similar examples but I have been unable to get it working with my data. 2 days later and I need an answer :(
基本上有一个 SQL Server 表,其中有一列包含 XML 数据.此数据包含我需要提取的值.
Basically have a SQL Server table with a column containing XML data. This data contains values I need to extract.
这是我的 XML.
<CustomFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd">
<Field fieldName="AutoCategory">Event Log</Field>
<Field fieldName="SType">Event Log</Field>
<Field fieldName="STag1">AgentGuid</Field>
<Field fieldName="STag2">AlertRegistrationId</Field>
<Field fieldName="STag3">LogType</Field>
<Field fieldName="SValue1">619764177412541</Field>
<Field fieldName="SValue2">104</Field>
<Field fieldName="SValue3">1380569194</Field>
<Field fieldName="SdTicketId">RPSv1006330</Field>
<Field fieldName="AgentName">bla bla</Field>
<Field fieldName="MachineGroupGuid">86115414719112271316891312</Field>
<Field fieldName="OrgFk">59165166782128125214185317</Field>
<Field fieldName="GuidAgent">619764177412541</Field>
<Field fieldName="AlertCount">0</Field>
<Field fieldName="TicketTitle">bla bla</Field>
<Field fieldName="LegacyId">152262</Field>
<Field fieldName="LegacyRef">152262</Field>
<Field fieldName="CwStatus">2</Field>
<Field fieldName="CwTicketId">89495</Field>
</CustomFields>
我需要能够提取与 CwTicketId
字段名称关联的值.
I need to be able to pull out the value associated to the CwTicketId
field name.
所以本质上,我想通过 XML 查找具有 fieldName = "CwTicketId"
的节点并返回 89495
或等效值.
So in essence, I want to look through the XML to find the node with fieldName = "CwTicketId"
and to return 89495
or equivalent value.
下面是我自己想出的代码,它提取值,但问题是有时 XML 的顺序不同,所以值并不总是在我指定的行上,因此它以准确的数据返回.
Below is the code I have come up with myself, which pulls values out, but the problem is sometimes the XML is ordered differently, so the values are not always on the line that I have specified, hence it returns in accurate data.
;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
SELECT
ref as ServiceDeskID,
sdSummary as ServiceDeskSummary,
customFields.value('(/CustomFields/Field/node())[17]', 'varchar(100)') as LegacyIDTicketing,
customFields.value('(/CustomFields/Field/node())[19]', 'varchar(100)') as CWIDTicketing
FROM
[ksubscribers].[kasadmin].[SDIncident]
我也需要第二个值,但如果我能弄清楚如何提取一个值,我可以复制另一个值.
The second value I also need, but if i can figure out how to pull one value out, I can duplicate for the other.
希望有人能帮忙,因为我已经开始脱发了!
Hope someone can help as I have started ripping my hair out!
感谢您的帮助!!
推荐答案
;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
select
T.C.value('data(.)', 'nvarchar(128)')
from [YOUR_TABLE] as Y
outer apply Y.[YOUR_XML_COLUMN].nodes('/CustomFields/Field[@fieldName="CwTicketId"]') as T(C)
这篇关于SQL Server 查询元素值的 xml 属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!