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

3  

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'
Mehrlein, Roswitha
SJM\MehrlR01
RMehrlein@SJM.com   
Roswita  Mehrlein, Beatrice Porta
SJM Germany
HR/Administration
Sales Representative     
x0lGQRQAAAABAAAAAAAAAAAeAQAyAAAAVgBAAAAA=
Sales
');

--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'',TheXmlAsString) + LEN(N'') 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'



    true
    
      1
      After successful deployment
      
      true
      com.atlassian.bamboo.triggers.atlassian-bamboo-triggers:afterSuccessfulDeployment
      
      
        
          deployment.trigger.afterSuccessfulDeployment.triggeringEnvironmentId
          19234819
        
      
    
  
  true
');

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;