CODEWITHSUNDEEP
sql-serverxmlt-sql
Timberline411
Timberline411

Reputation: 35

NULL Value on Attribute when reading XML in TSQL

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>

results

Upvotes: 0

Views: 311

Answers (2)

Thom A
Thom A

Reputation: 95561

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);

Upvotes: 1

Anssssss
Anssssss

Reputation: 3262

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)

Upvotes: 1

Related Questions