I have a XML file like this:

<?xml version=\"1.0\" encoding=\"utf-8\"?>
<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" 
               xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" 
               xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
    <soap:Body>
        <Abc xmlns=\"http://www.web.xds.co.za/XDSConnectWS\">
            <Def>ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>

I want to write a stored procedure that extracts the text inside the DEF tag and get column data like Id, abcname and insert into a SQL Server database.

Any help in this issue will be greatly appreciated.

Thank You!

CodePudding user response：

Extracting the value of <Def> as string, then parse it as xml, since the xml don't have any root node, will add a node so we can use xml function in sql server to get the node values.

declare @xmlstring varchar(max) 
declare @xml xml
set @xmlstring = N'<?xml version=\"1.0\" encoding=\"utf-8\"?>
<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" 
               xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" 
               xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
    <soap:Body>
        <Abc xmlns=\"http://www.web.xds.co.za/XDSConnectWS\">
            <Def>&lt;ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&gt;</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>';

set @xmlstring = substring(@xmlstring, charindex('<Def>', @xmlstring) 5, charindex('</Def>', @xmlstring) - charindex('<Def>', @xmlstring) -5)

set @xml = concat('<root>', cast (@xmlstring as xml).value('.[1]','nvarchar(max)' ), '</root>');

select @xml;
select @xml.value('(/root/ID)[1]','varchar(30)') as ID
    ,  @xml.value('(/root/AbcName)[1]','varchar(30)') as [Name]

CodePudding user response：

The problem here is that the XML nodes inside Def are not encoded properly. They are stored as inner text within the node. So you need to pull it out and convert it.

We can pull out the text using .value and the CAST it, inside a CROSS APPLY (VALUES.

You can either do this using direct .value calls

DECLARE @xml xml = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" 
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <Abc xmlns="http://www.web.xds.co.za/XDSConnectWS">
            <Def>&lt;ID&gt;1&lt;/ID&gt;&lt;Number&gt;Not Available&lt;/Number&gt;&lt;AbcName&gt;Hello&lt;/AbcName&gt;</Def>
        </Abc>
    </soap:Body>
</soap:Envelope>';

WITH XMLNAMESPACES(
  'http://www.web.xds.co.za/XDSConnectWS' AS x,
  'http://www.w3.org/2003/05/soap-envelope' AS soap
)
SELECT
  v.InnerXml.value('(ID/text())[1]','int') as ID,
  v.InnerXml.value('(AbcName/text())[1]','varchar(30)') as Name,
  v.InnerXml.value('(Number/text())[1]','varchar(30)') as Number
FROM (VALUES(
    CAST(@xml.value('(soap:Envelope/soap:Body/x:Abc/x:Def/text())[1]','nvarchar(max)') AS xml)
)) v(InnerXml);

Or if you have multiple nodes to read then you can feed it through .nodes:

WITH XMLNAMESPACES(
  'http://www.web.xds.co.za/XDSConnectWS' AS x,
  'http://www.w3.org/2003/05/soap-envelope' AS soap
)
SELECT
  x2.n.value('(ID/text())[1]','int') as ID,
  x2.n.value('(AbcName/text())[1]','varchar(30)') as Name,
  x2.n.value('(Number/text())[1]','varchar(30)') as Number
FROM @xml.nodes('soap:Envelope/soap:Body/x:Abc/x:Def') x1(DEF)
CROSS APPLY (VALUES(
    CAST(N'<r>'   x1.DEF.value('text()[1]','nvarchar(max)')   '</r>' AS xml)
)) v(InnerXml)
CROSS APPLY v.InnerXml.nodes('r') x2(n);

db<>fiddle