How to handle empty tag in XML with Xquery

[Sudip_inn]

See my code

DECLARE @INPUTXML XML =
'<Lineitems>
<Lineitem>
<Ticker>TER</Ticker>
<ID>0</ID>
<LineItem>Net Revenue</LineItem>
<XFundCode>TRIN</XFundCode>
<UserID>TDP</UserID>
</Lineitem>
<Lineitem>
<Ticker>TER</Ticker>
<ID>0</ID>
</LineItem>
<XFundCode>XXP</XFundCode>
<UserID>TDP</UserID>
</Lineitem>
</Lineitems>';

DECLARE @Ticker AS VARCHAR(MAX)
, @ID AS INT
, @Lineitem AS VARCHAR(MAX)
, @XFundCode AS VARCHAR(MAX)
, @UserID AS VARCHAR(MAX);

-- count total number of items
DECLARE @i INT
, @cnt INT = @INPUTXML.value('count(/Lineitems/Lineitem)', 'INT');

-- loop through XML item by item
SET @i = 1;
WHILE @i <= @cnt
BEGIN

SELECT @Ticker = col.value('(Ticker/text())[1]','VARCHAR(MAX)')
, @ID = col.value('(ID/text())[1]','INT')
, @Lineitem = nullif('',col.value('(LineItem/text())[1]','VARCHAR(MAX)'))
, @XFundCode = col.value('(XFundCode/text())[1]','VARCHAR(MAX)')
, @UserID = col.value('(UserID/text())[1]','VARCHAR(MAX)')
FROM @INPUTXML.nodes('/Lineitems/Lineitem[position() = sql:variable("@i")]') AS tab(col);

-- do whatever needed in the loop here
Print @Lineitem

SET @i += 1;
END





1) when i am trying to execute the above code then i am getting error called

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 12, character 15, end tag does not match start tag

may be it is happening due to one empty tag like

<Lineitem>
<Ticker>TER</Ticker>
<ID>0</ID>
</LineItem>
<XFundCode>XXP</XFundCode>
<UserID>TDP</UserID>
</Lineitem>


Here line item is empty like </LineItem>

how to handle this situation with empty tag. what to fix in my above code as a result code should execute.

2) when i am iterate in above xml in loop. if any line item is empty then i will not do anything. how to determine my line item is empty or not ?

please guide me with code example. thanks

Continue reading...