Update Empty Xml Tag in SQL Server

Update Empty XML Tag in SQL Server

As far as I know from my own experience, you cannot do this in one step, since the <AgencyID/> element really has no text() - so therefore, you cannot replace it.

You might have to use something like:

DECLARE @newValue VARCHAR(100)
SELECT @newValue = '01'

-- first update - add a new <AgencyID>...</AgencyID> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('insert <AgencyID>{sql:variable("@newValue")}</AgencyID> as last into (/CodeFiveReport/Owner)[1]') 
WHERE id = 'myId'

-- second update - remove the empty <AgencyID /> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('delete (/CodeFiveReport/Owner/AgencyID)[1]') 
WHERE id = 'myId'

One thing: you need to use the XmlTest.modify operation - not XmlTest.insert as you have in your post. There is no .insert() function on SQL Server XML columns.

How to display empty XML tag in MS SQL Server

As I mention in the comments, wrap the value with ISNULL to return a blank string:

SELECT Fname,
       LName,
       ISNULL(MName,'') AS MName
FROM (VALUES('Rohit','Kumar',NULL))V(FName,LName,MName)
FOR XML PATH('Details'),ROOT('EmpDetails'); --Nodes can't have spaces in their names

This returns the below XML:

<EmpDetails>
    <Details>
        <Fname>Rohit</Fname>
        <LName>Kumar</LName>
        <MName></MName>
    </Details>
</EmpDetails>

Note that SQL Server does not generate blank nodes using the shorter syntax, it will explicitly open and close the tags.

Replacing Value of Empty Node in SQL XML

Will this be of any help: http://whyiamright.wordpress.com/2008/01/02/updating-xml-column-in-sql-server-2005/?

You seem to miss slash in the begining of xpath.

EDIT: Then it seems to be a duplicate of this question:

Update Empty XML Tag in SQL Server

Select query with SQL XML empty element

You can ISNULL it to an empty string

SELECT Data.value('(/Data/Reference)[1]', 'nvarchar(10)') as  PolicyNumber
       ,[RequestId]
       ,ISNULL([BatchEntryId], '') AS BatchEntryId
       ,[StatusCode]
       ,[PaymentMethodCode]
       ,Data.value('(/Data/Amount)[1]', 'nvarchar(10)') as  Amount
    FROM [dbo].[TransmissionData]
    WHERE RequestId = 2031070233
    FOR XML RAW ('RequestRecord'), ELEMENTS, TYPE

If BatchEntryId is not varchar or nvarchar you should cast it first

ISNULL(CAST(BatchEntryId AS varchar(30)), '') AS BatchEntryId

---

Note that SQL Server generates it as

<BatchEntryId></BatchEntryId>

however this is semantically equivalent in XML to

<BatchEntryId/>

How to populate an empty XML node using modify replace on an XML column?

Element <Comments/> has no text() node, you should do insert rather then replace:

    update @t1 
    set userdef.modify('
        insert text{"NO COMMENTS"}
        into (/Orders/Document/Comments[1])[1]
    ')
    where id = @id;

If one wishes to insert text from sql variable, the following construct can be used:

declare @comments varchar(1000);
set @comments = 'NO COMMENTS';

update @t1 
set userdef.modify('
    insert text {sql:variable("@comments")}
    into (/Orders/Document/Comments[1])[1]
')
where id = @id;

Update XML field with no text in T-SQL

This node (/filemeta/description/text())[1] does not exist in the XML so there is nothing to replace. You have to do an insert instead. If you have a scenario where you have a mix of empty nodes and nodes with a value you have to run two update statements.

declare @filemetaDB table(filemeta xml)

insert into @filemetaDB values
('<filemeta><description>Not empty</description></filemeta>'), -- Not empty node
('<filemeta><description/></filemeta>'),                       -- Empty node
('<filemeta></filemeta>')                                      -- Missing node

-- Replace value for nodes with value
update @filemetaDB
set filemeta.modify('replace value of (/filemeta/description/text())[1] with "TEST 1"')
where filemeta.exist('/filemeta/description/text()') = 1

-- Add text node for empty nodes
update @filemetaDB
set filemeta.modify('insert text{"TEST 2"} into (/filemeta/description)[1]')
where filemeta.exist('/filemeta/description/text()') = 0

select *
from @filemetaDB

Result:

filemeta
------------------------------------------------------
<filemeta><description>TEST 1</description></filemeta>
<filemeta><description>TEST 2</description></filemeta>
<filemeta />

How to import import blank XML tags into a Number column of a table using a stored procedure

I assume you are receiving 0 or conversion errors for rows with this tag containing empty string.

You can shred XML to a varchar value and then do verification and type conversions in TSQL:

 SELECT
    [Weight] =
       CASE WHEN ISNULL(SHPReportData.value('(RawDataItem/Weight)[1]', 'VARCHAR(15)'),'') = '' THEN NULL
       ELSE CAST(SHPReportData.value('(RawDataItem/Weight)[1]', 'VARCHAR(15)') AS DECIMAL(10,2)) END,
     [WeightUQ] = SHPReportData.value('(RawDataItem/UQ)[1]', 'VARCHAR(2)')
  FROM
     @XML.nodes('RawData') c (SHPReportData);

SQL Server query empty xml element value

It looks like your two queries are against different XML sources. In the first you have referencedpublicationid as a element name and in the second you have it as an attribute called @fieldName. I also have a hard time understanding what the resulting XML has to do with this. xsi:nil="true" is generated when you create XML using for xml path, elements xsinil.

I will instead try to address what I think is the issue you see.

Try this code:

declare @XML xml = '<referencedpublicationid />'
select @XML.value('./referencedpublicationid[1]', 'nvarchar(50)')
select @XML.value('(./referencedpublicationid/text())[1]', 'nvarchar(50)')

The first query returns an empty string and the second returns NULL.

If you instead specify the data type uniqueidentifier the first will fail because you can not cast an empty string to uniqueidentifier. The second converts fine since it is NULL.

All values in untyped XML in SQL Server are strings that is then converted to the specified data type in the values function.

When you use ./referencedpublicationid[1] you will get back the concatenated value of all sub text() values. SQL Server for some reason gives you an empty string when there are no text() nodes to be found.

This expression (./referencedpublicationid/text())[1] specifies the first text() node in referencedpublicationid and since there are none you get the result of NULL.

---

Related Topics