NULL Value on Attribute when reading XML in TSQL

Question

There are 3 attributes I am trying to return when reading this xml file. 2 of the 3 are returning expected values. I can not figure out why I can't get the id to return the value. It always returns NULL. How can I get the proper value of rid1 and rid2

DECLARE @xml xml
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'C:\data\workbook.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES (default 
'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ,                    
'http://schemas.openxmlformats.org/officeDocument/2006/relationships' as a,
'http://schemas.openxmlformats.org/markup-compatibility/2006' as b,
'http://schemas.microsoft.com/office/spreadsheetml/2010/11/main' as c,                   
'http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac' as d)

SELECT
 doc.col.value('@name', 'nvarchar(10)') sheet
,doc.col.value('@id', 'nvarchar(max)') rid 
,doc.col.value('@sheetId', 'int') id 
FROM @xml.nodes('*:workbook/sheets/sheet') doc(col)

Here is the XML File

    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x15">
  <fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="18431" />
  <workbookPr defaultThemeVersion="166925" />
  <mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
    <mc:Choice Requires="x15">
      <x15ac:absPath xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac" url="C:\data\" />
    </mc:Choice>
  </mc:AlternateContent>
  <bookViews>
    <workbookView xWindow="0" yWindow="0" windowWidth="21576" windowHeight="7968" />
  </bookViews>
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1" />
    <sheet name="Sheet2" sheetId="2" r:id="rId2" />
  </sheets>
  <calcPr calcId="171027" />
  <fileRecoveryPr repairLoad="1" />
  <extLst>
    <ext xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" uri="{140A7094-0E35-4892-8432-C4D2E57EDEB5}">
      <x15:workbookPr chartTrackingRefBase="1" />
    </ext>
  </extLst>
</workbook>

Anssssss · Accepted Answer · 2018-06-22 15:49:06Z

1

You need to prefix the attribute with the namespace like so:

SELECT
 doc.col.value('@name', 'nvarchar(10)') sheet
,doc.col.value('@a:id', 'nvarchar(max)') rid 
,doc.col.value('@sheetId', 'int') id 
FROM @xml.nodes('*:workbook/sheets/sheet') doc(col)

answered Jun 22, 2018 at 15:49

Anssssss

3,3411 gold badge34 silver badges44 bronze badges

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

Comments

Thom A · Accepted Answer · 2018-06-22 15:50:23Z

1

Give your namespaces the same aliases in your T-SQL as they have in your XML and use the namespace alias in your reference:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
                    'http://schemas.openxmlformats.org/officeDocument/2006/relationships' AS r,
                    'http://schemas.openxmlformats.org/markup-compatibility/2006' AS mc,
                    'http://schemas.microsoft.com/office/spreadsheetml/2010/11/main' AS x15,
                    'http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac' AS x15ac)
SELECT doc.col.value('@name', 'nvarchar(10)') sheet,
       doc.col.value('@r:id', 'nvarchar(max)') rid,
       doc.col.value('@sheetId', 'int') id
FROM @xml.nodes('*:workbook/sheets/sheet') doc(col);

answered Jun 22, 2018 at 15:50

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

2 Comments

Timberline411 Over a year ago

Not making a comment on proper form, but you don't have to make them the same alias as XML doc. I had like you suggest initially, but then changed during debug process to see if my namespace was not proper. The name space declaration was not the issue is was not prefixing @id. I like the way your code looks, so +1

Thom A Over a year ago

@Timberline411 You don't have to, no, but it does help. :)

Collectives™ on Stack Overflow

NULL Value on Attribute when reading XML in TSQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related