CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-server-2012
SF Developer
SF Developer

Reputation: 5384

Parse nvarchar value that looks like a simplified XML

I have a column of type nvarchar(1000) that contains something that looks like an XML:

<Master>  
   <NodeA>lorem ipsum</NodeA>  
   <NodeB>lorem ipsum</NodeB>  
   <NodeC>lorem ipsum</NodeC>  
   <NodeD>lorem ipsum</NodeD>  
</Master>

The value might have some carriage return and new lines embedded on it.

What would be the easiest way to get the value inside NodeA?

I've tried to remove the hardcoded string value <masterA> but then I feel I'm doing something wrong here.

Upvotes: 2

Views: 231

Answers (1)

marc_s
marc_s

Reputation: 755013

Try this:

DECLARE @XmlTable TABLE (ID INT NOT NULL, XmlContent NVARCHAR(1000))

INSERT INTO @XmlTable (ID, XmlContent)
VALUES (1, N'<Master>  
   <NodeA>lorem ipsum</NodeA>  
   <NodeB>lorem ipsum</NodeB>  
   <NodeC>lorem ipsum</NodeC>  
   <NodeD>lorem ipsum</NodeD>  
</Master>')

SELECT
    CAST(XmlContent AS XML).value('(/Master/NodeA)[1]', 'varchar(50)')
FROM
    @XmlTable
WHERE
    ID = 1

But if your column really only stores XML - you should make it an XML column - that's easier (no need to always do a CAST(... AS XML) before applying any XQuery methods), and it's also optimized in terms of storage.

Upvotes: 2

Related Questions