CODEWITHSUNDEEP
sql-serverxmlt-sqlparsing
babboon
babboon

Reputation: 793

T-SQL parse XML data into single line

I have XML saved into a column in a table as type nvarchar. Now I need to parse data from that xml. I do

SELECT
    CONVERT(XML, columnX).value('(chatTranscript/message/msgText/text())[1]', 'nvarchar(max)')

as chat but I get only first value. How do I extract all into single line? XML can be long, depends on chat length.

I need to get userNick and then msgText and loop it till the end. Something like this:

userX:Hello<>userY:How are you;

XML:

<?xml version="1.0"?>
<chatTranscript startAt="2020-07-30T11:00:12Z" sessionId="......">
    <newParty userId="......" timeShift="0" visibility="ALL" eventId="1">
        <userInfo personId="" userNick="userX"/>
    </newParty>
    <message userId="..." timeShift="12" visibility="ALL" eventId="9">
        <msgText msgType="text">Hello</msgText>
    </message>
    <newParty userId="..." timeShift="15" visibility="ALL" eventId="10">
        <userInfo userNick="userY"/>
    </newParty>
    <message userId="..." timeShift="29" visibility="ALL" eventId="12">
        <msgText treatAs="NORMAL">how are you?</msgText>
    </message>
    <partyLeft userId="..." timeShift="36" visibility="ALL" eventId="13" askerId="...">
        <reason code="1">left with request to close if no agents</reason>
    </partyLeft>
    <partyLeft userId="..." timeShift="36" visibility="ALL" eventId="14" askerId="...">
        <reason code="4">removed by other party</reason>
    </partyLeft>
</chatTranscript>

Upvotes: 0

Views: 426

Answers (2)

babboon
babboon

Reputation: 793

Here is my solution:

ALTER FUNCTION [dbo].[fn_parse_chat_xml] (@xml XML) 
RETURNS NVARCHAR(MAX) 
BEGIN 
    DECLARE @n INT, @content NVARCHAR(MAX), @userId NVARCHAR(200), @userNick1 NVARCHAR(200), @userNick2 NVARCHAR(200), @userNickX NVARCHAR(200)
    SET @n = 1 
    SET @userId = @xml.value('(chatTranscript/newParty/@userId)[1]', 'nvarchar(max)')
    SET @userNick1 = @xml.value('(chatTranscript/newParty/userInfo/@userNick)[1]', 'nvarchar(max)')
    SET @userNick2 = @xml.value('(chatTranscript/newParty/userInfo/@userNick)[2]', 'nvarchar(max)')
    WHILE DATALENGTH(@xml.value('(chatTranscript/message/msgText/text())[sql:variable("@n")][1]', 'nvarchar(max)'))>0 
        BEGIN        
            IF @userId = @xml.value('(chatTranscript/message/@userId)[sql:variable("@n")][1]', 'nvarchar(max)') 
                SET @userNickX = @userNick1
            else 
                SET @userNickX = @userNick2 
            SET @content = concat(@content, ' <> ', @userNickX, ': ', @xml.value('(chatTranscript/message/msgText/text())[sql:variable("@n")][1]', 'nvarchar(max)'))
            SET @n = @n + 1
        END
    RETURN @content
END

Upvotes: 0

kaladin_storm
kaladin_storm

Reputation: 352

You need code to do this cleanly. Trying to do what you are asking will be super messy T-SQL. I'd recommend parsing the xml in code to generate what you want based on that xml. You could also create a CLR function using code so that you can create a SQL function to do this. You can do some amazing things with XQuery and T-SQL, but sometimes it just gets to messy. For xml manipulation all within the database, CLR functions are perfect.

Upvotes: 1

Related Questions