loading xml File into SQL Server table is not working.

Question

I have XML file that I am trying to load into SQL server but when I run the script, it is not displaying any rows.

<root>
  <DeviceRecord xmlns="http://www.archer-tech.com/">
    <IP>137.52</IP>
    <FQDN>sdcww00</FQDN>
    <NetBios_Name></NetBios_Name>
    <Operating_System>Microsoft Windows Vista</Operating_System>
    <Mac_Address></Mac_Address>
    <Confidence_Level>65
</Confidence_Level>
  </DeviceRecord>
  <DeviceRecord xmlns="http://www.archer-tech.com/">
    <IP>155.37.51</IP>
    <FQDN>ww00048</FQDN>
    <NetBios_Name></NetBios_Name>
    <Operating_System>Microsoft Windows Vista</Operating_System>
    <Mac_Address></Mac_Address>
    <Confidence_Level>65
</Confidence_Level>
</DeviceRecord>
</root>

SQL Script

declare @xmldata as xml

set @xmldata= (SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Users\ag03536\Documents\New folder\updated.xml', SINGLE_BLOB)as X)

SELECT
    x.Rec.query('./DeviceRecord').value('.','varchar(120)')
    ,x.Rec.query('./IP').value('.','varchar(20)')
    ,x.Rec.query('./FQDN').value('.','varchar(20)')
FROM @xmldata.nodes('./root') as x(rec)

Gottfried Lesigang · Accepted Answer · 2017-08-17 19:12:40Z

4

First you have to check, whether the XML is read propperly. Use this after reading your XML into the variable:

SELECT @xmldata;

Secondly all your values live in a default namespace. You have to declare it:

WITH XMLNAMESPACES(DEFAULT 'http://www.archer-tech.com/')

Third, your query should read all nested <DeviceRecord> entries probably, you need .nodes() down to this level. The full query should be something like this:

WITH XMLNAMESPACES(DEFAULT 'http://www.archer-tech.com/')
SELECT
     x.Rec.value('(IP/text())[1]','varchar(20)') AS DevRec_ID
    ,x.Rec.value('(FQDN/text())[1]','varchar(20)') AS DevRec_FQDN
    --The rest should be the same approach...
FROM @xmldata.nodes('/*:root/DeviceRecord') as x(rec)

EDIT: Your node `<root>` is not part of the default namespace.

I used a wildcard (*:root)

answered Aug 17, 2017 at 19:12

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

loading xml File into SQL Server table is not working.

1 Answer 1

EDIT: Your node `<root>` is not part of the default namespace.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT: Your node <root> is not part of the default namespace.

Comments

Your Answer

Sign up or log in

Post as a guest

Related

EDIT: Your node `<root>` is not part of the default namespace.