SQL Server XML parse issue

Question

I need to parse XML into a SQL Server 2012 database. However, I cannot find any good guide to parse this kind XML (here is SELECT TOP 2 FROM table):

<ns2:SoftWare xmlns:ns2="http://www.example.com" xmlns:ns3="http://www.example2.com"><keyc>123-ABC</keyc><statusc>Y</statusc></ns2:SoftWare>
<ns2:custom-data xmlns:ns2="http://www.example.com/2"><timec>2016.01.02</timec><customer>8R</customer><keyc>8R</keyc><statusc>N</statusc></ns2:custom-data>

Any help, how I can parse "keyc" value from XML?

So, I can use it select clause / or insert it to database.

Jason W · Accepted Answer · 2017-04-06 12:55:15Z

3

You can use the nodes and value to get that entity:

DECLARE @Data TABLE (XmlText XML)
INSERT @Data VALUES
    ('<ns2:SoftWare xmlns:ns2="http://www.example.com" xmlns:ns3="http://www.example2.com"><keyc>123-ABC</keyc><statusc>Y</statusc></ns2:SoftWare>'),
    ('<ns2:custom-data xmlns:ns2="http://www.example.com/2"><timec>2016.01.02</timec><customer>8R</customer><keyc>8R</keyc><statusc>N</statusc></ns2:custom-data>')

SELECT
    Nodes.KeyC.value('.', 'VARCHAR(50)') AS KeyC
FROM @Data D
    CROSS APPLY XmlText.nodes('//keyc') AS Nodes(KeyC)

This outputs the following:

KeyC
-----------
123-ABC
8R

answered Apr 6, 2017 at 12:55

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server XML parse issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related