如何从sql中的ntext (xml格式)列获取值

I have a column in my SQL database that is called Triggers_xml_data and its type is ntext. The column is in a xml format and I am trying to get a value from a certain part of the xml. I seen an example of this being done without a column like this:

我的SQL数据库中有一个列叫做Triggers_xml_data，它的类型是ntext。这个列是xml格式的，我正在尝试从xml的某个部分获取值。我看到过这样一个例子:

declare @fileContent xml
set @fileContent ='<my:Header>
<my:Requestor>Mehrlein, Roswitha</my:Requestor>
<my:RequestorUserName>SJM\MehrlR01</my:RequestorUserName>
<my:RequestorEmail>[e-mail]</my:RequestorEmail>   
<my:HRContact>Roswita  Mehrlein, Beatrice Porta</my:HRContact>
<my:Entity>SJM Germany</my:Entity>
<my:Department>HR/Administration</my:Department>
<my:PositionTitle>Sales Representative</my:PositionTitle>     
<my:JobDescription>x0lGQRQAAAABAAAAAAAAAAAeAQAyAAAAVgBAAAAA=</my:JobDescription>
<my:PositionDepartment>Sales</my:PositionDepartment>'
 
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-08-29T12-58-51' as my)
select @fileContent.value('(//my:PositionDepartment)[1]', 'varchar(255)')

But I want to select my column like this:

但我想这样选择我的专栏:

Declare @filevalue xml

select de.triggers_xml_data
from dbo.DEPLOYMENT_ENVIRONMENT as de

But this is not working and I tried to use this @filecontent.value('(//value)[1]','varchar(255)') and making it equal the column value, I have tried casting it but I can't find a way to do this. Is this possible?

但是这不起作用，我尝试使用这个@filecontent.value('(//value)[1]'，'varchar(255)')并使它等于列值，我试过对它进行强制转换，但是我找不到这样做的方法。这是可能的吗?

When I do this:

当我这样做:

SELECT 
CAST(
    REPLACE(CAST(de.TRIGGERS_XML_DATA AS VARCHAR(MAX)), 'encoding="utf-16"', '')
  AS XML).value('(triggers/triggerDefinition/config/item/value)[1]', 'NVARCHAR(max)') as Item, de.ENVIRONMENT_ID
from dbo.DEPLOYMENT_ENVIRONMENT as de
where de.ENVIRONMENT_ID = 19234819

I am getting a null value returned.

我得到的是一个空值。

Here is an example of what my xml could look like:

下面是我的xml的一个示例:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<configuration xml:space="preserve">

<triggers>
    <defined>true</defined>
    <triggerDefinition>
      <id>1</id>
      <name>After successful deployment</name>
      <userDescription/>
      <isEnabled>true</isEnabled>
      <pluginKey>com.atlassian.bamboo.triggers.atlassian-bamboo-triggers:afterSuccessfulDeployment</pluginKey>
      <triggeringRepositories/>
      <config>
        <item>
          <key>deployment.trigger.afterSuccessfulDeployment.triggeringEnvironmentId</key>
          <value>19234819</value>
        </item>
      </config>
    </triggerDefinition>
  </triggers>
  <bambooDelimiterParsingDisabled>true</bambooDelimiterParsingDisabled>
</configuration>

1 个解决方案

#1

The XML, as you posted it, is not valid. Your code example does not work... It is not allowed to use a namespace prefix without a namespace declaration. Furthermore your example misses the closing Header-tag...

正如您发布的那样，XML是无效的。您的代码示例不起作用……不允许使用没有名称空间声明的名称空间前缀。此外，您的示例忽略了结束标记……

I corrected this...

我纠正了这个……

DECLARE @yourTbl TABLE(ID INT, YourXML NTEXT);
INSERT INTO @yourTbl VALUES
(1,N'<my:Header xmlns:my="DummyUrl">
<my:Requestor>Mehrlein, Roswitha</my:Requestor>
<my:RequestorUserName>SJM\MehrlR01</my:RequestorUserName>
<my:RequestorEmail>[e-mail]</my:RequestorEmail>   
<my:HRContact>Roswita  Mehrlein, Beatrice Porta</my:HRContact>
<my:Entity>SJM Germany</my:Entity>
<my:Department>HR/Administration</my:Department>
<my:PositionTitle>Sales Representative</my:PositionTitle>     
<my:JobDescription>x0lGQRQAAAABAAAAAAAAAAAeAQAyAAAAVgBAAAAA=</my:JobDescription>
<my:PositionDepartment>Sales</my:PositionDepartment>
</my:Header>');

--Lazy approach
SELECT ID
      ,CAST(CAST(YourXml AS NVARCHAR(MAX)) AS XML).value(N'(//*:PositionDepartment)[1]','nvarchar(max)')
FROM @yourTbl;


--explicit approach
WITH XMLNAMESPACES('DummyUrl' AS my)
SELECT ID
      ,CAST(CAST(YourXml AS NVARCHAR(MAX)) AS XML).value(N'(/my:Header/my:PositionDepartment)[1]','nvarchar(max)')
FROM @yourTbl

Some Background

If possible you should not store XML in other format than XML and further more one should avoid NTEXT, as it is depricated since SS2005!.

如果可能的话，您不应该以XML以外的格式存储XML，而且应该更多地避免使用NTEXT，因为自从SS2005以来，NTEXT就被剥夺了。

You have to cast NTEXT to NVARCHAR(MAX) first, than cast this to XML. The second will break, if the XML is not valid. That means: If the XML is really the way you posted it, this cannot work!

您必须首先将NTEXT转换为NVARCHAR(MAX)，而不是将其转换为XML。如果XML无效，则第二个将中断。这意味着:如果XML真的是您发布的方式，那么它就不能工作!

UPDATE: String-based approach, if XML does not work

If you cannot cast this to XML you might try this

如果不能将其转换为XML，可以尝试使用这个

--String based
WITH Casted AS
(
    SELECT ID
          ,CAST(YourXML AS NVARCHAR(MAX)) AS TheXmlAsString
    FROM @yourTbl
)
,WithPosition AS
(
    SELECT Casted.*
          ,CHARINDEX(N'<my:PositionDepartment>',TheXmlAsString) + LEN(N'<my:PositionDepartment>') AS FirstLetter
    FROM Casted
)
SELECT ID 
      ,SUBSTRING(TheXmlAsString,FirstLetter,CHARINDEX('<',TheXmlAsString,FirstLetter)-FirstLetter)
FROM WithPosition

UPDATE 2

According to your edit the following returns a NULL value. This is good, because it shows, that the cast was successfull.

根据您的编辑，下面返回一个空值。这很好，因为它表明，演员阵容是成功的。

SELECT 
CAST(
    REPLACE(CAST(de.TRIGGERS_XML_DATA AS VARCHAR(MAX)), 'encoding="utf-16"', '')
  AS XML).value('(triggers/triggerDefinition/config/item/value)[1]',
'NVARCHAR(max)') as Item, de.ENVIRONMENT_ID
from dbo.DEPLOYMENT_ENVIRONMENT as de
where de.ENVIRONMENT_ID = 19234819

Try this (skip namespace with wildcard):

试试这个(用通配符跳过名称空间):

SELECT 
CAST(
    REPLACE(CAST(de.TRIGGERS_XML_DATA AS VARCHAR(MAX)), 'encoding="utf-16"', '')
  AS XML).value('(*:triggers/*:triggerDefinition/*:config/*:item/*:value)[1]', 'NVARCHAR(max)') as Item, de.ENVIRONMENT_ID
from dbo.DEPLOYMENT_ENVIRONMENT as de
where de.ENVIRONMENT_ID = 19234819

And this should be even better:

这应该更好:

SELECT 
CAST(CAST(de.TRIGGERS_XML_DATA AS NVARCHAR(MAX)) AS XML).value('(*:triggers/*:triggerDefinition/*:config/*:item/*:value)[1]', 'NVARCHAR(max)') as Item, de.ENVIRONMENT_ID
from dbo.DEPLOYMENT_ENVIRONMENT as de
where de.ENVIRONMENT_ID = 19234819

UPDATE 3

I'd rather cut away the full declaration. Your posted example would go like this

我宁愿删去全部的宣言。您发布的示例应该是这样的

DECLARE @DEPLOYMENT_ENVIRONMENT TABLE(ENVIRONMENT_ID INT, TRIGGERS_XML_DATA NTEXT);
INSERT INTO @DEPLOYMENT_ENVIRONMENT VALUES
(19234819,N'<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<configuration xml:space="preserve">

<triggers>
    <defined>true</defined>
    <triggerDefinition>
      <id>1</id>
      <name>After successful deployment</name>
      <userDescription/>
      <isEnabled>true</isEnabled>
      <pluginKey>com.atlassian.bamboo.triggers.atlassian-bamboo-triggers:afterSuccessfulDeployment</pluginKey>
      <triggeringRepositories/>
      <config>
        <item>
          <key>deployment.trigger.afterSuccessfulDeployment.triggeringEnvironmentId</key>
          <value>19234819</value>
        </item>
      </config>
    </triggerDefinition>
  </triggers>
  <bambooDelimiterParsingDisabled>true</bambooDelimiterParsingDisabled>
</configuration>');

WITH Casted AS
(
    SELECT CAST(de.TRIGGERS_XML_DATA AS NVARCHAR(MAX)) AS XmlAsSting
    FROM @DEPLOYMENT_ENVIRONMENT as de
    where de.ENVIRONMENT_ID = 19234819
)
SELECT CAST(SUBSTRING(XmlAsSting,CHARINDEX('?>',XmlAsSting)+2,8000) AS XML).value('(/*:configuration/*:triggers/*:triggerDefinition/*:config/*:item/*:value)[1]', 'NVARCHAR(max)') as Item
FROM Casted;