Convert String To XML

Question

How to convert the below string to XML,

SET @string = '<Field>
<Field Name="'+@Cname+'">
<DataField>'+@Cname+'</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>'

I tried SET @xmlstring = CONVERT(XML,@string) but it displays below error Msg 9459, Level 16, State 1, Line 17 XML parsing: line 4, character 13, undeclared prefix

Answer 1

Oh no! Never create XML via string concatenation! Just imagine, your variable comes with a value like this -> value or Tim, Tom & Fred... This might work perfectly, pass all tests and break after rollout with undefined errors.

Always use SQL-Servers support to create XML:

DECLARE @cname VARCHAR(100)='Some Name';

DECLARE @xml XML=
(
    SELECT  @cname AS [Field/@Name]
           ,@cname AS [DataField]
           ,'System.String' AS [TypeName]
    FOR XML PATH('Field')
);
SELECT @xml;

The result

<Field>
  <Field Name="Some Name" />
  <DataField>Some Name</DataField>
  <TypeName>System.String</TypeName>
</Field>

And here with the namespace:

WITH XMLNAMESPACES('Some.namespace.url' AS rd)
SELECT @xml=
( 
    SELECT  @cname AS [Field/@Name]
            ,@cname AS [DataField]
            ,'System.String' AS [rd:TypeName]
    FOR XML PATH('Field')
);
SELECT @xml

The result

<Field xmlns:rd="Some.namespace.url">
  <Field Name="Some Name" />
  <DataField>Some Name</DataField>
  <rd:TypeName>System.String</rd:TypeName>
</Field>