SQL Server查询元素值的xml属性

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 :(

对不起，如果这是在其他地方，我发现了很多类似的例子，但我一直无法使用我的数据。 2天后，我需要一个答案:(

Basically have a SQL Server table with a column containing XML data. This data contains values I need to extract.

基本上有一个SQL Server表，其中包含一个包含XML数据的列。此数据包含我需要提取的值。

Here is my XML.

这是我的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>

I need to be able to pull out the value associated to the CwTicketId field name.

我需要能够提取与CwTicketId字段名称关联的值。

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来查找具有fieldName =“CwTicketId”的节点并返回89495或等效值。

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.

下面是我自己提出的代码，它将值拉出来，但问题是有时XML的排序方式不同，因此值并不总是在我指定的行上，因此它返回准确的数据。

;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!

希望有人可以提供帮助，因为我已经开始撕掉我的头发了！

Thanks for the help!!

谢谢您的帮助！！

2 个解决方案

#1

;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)

#2

Try this:

尝试这个：

;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
SELECT 
    ref as ServiceDeskID, 
    sdSummary as ServiceDeskSummary,
    CwTicketID = customFields.value('(/CustomFields/Field[@fieldName="CwTicketId"]/text())[1]', 'int')
FROM  
   [ksubscribers].[kasadmin].[SDIncident]

This selected the <Field> node that has the CwTicketId as its field name attribute - and this will work always, no matter how the XML and its nodes are ordered (as long as the fieldName value stays CwTicketId).

这选择了具有CwTicketId作为其字段名称属性的 节点 - 无论XML及其节点如何排序（只要fieldName值保持CwTicketId），这将始终有效。