SQL Server : select attribute from xml where attribute name is defined by namespace

I couldnt find anything specifically on this, but I would like to know how I can select an attribute of a sub-node in XML that is identified by a namespace, asp per the XLINK namespace below:

<?xml version="1.0" encoding="UTF-8"?>
<result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xlink="http://www.w3.org/1999/xlink">
  <people lastUpdateDate="xxxx-xx-xx">
    <person name="Paul" surname="Who Cares" height="180" weight="89" xlink:href="https://i.stack.imgur.com/jMgYU.jpg?s=48&g=1" />
  </people>
</result>

Currently my query looks like this, but I just need a small alteration in order to get the link.

DECLARE @xml AS TABLE(MyXml xml);

INSERT @xml SELECT CONVERT(xml, BulkColumn, 2) 
FROM OPENROWSET (Bulk '[filepath]', SINGLE_BLOB) [rowsetresults];

WITH xmlnamespaces('http://www.w3.org/1999/xlink' AS a)
SELECT
      Person.value('@name', 'NVARCHAR(50)'),
      Person.value('@surname', 'NVARCHAR(50)'),
      Person.value('@height', 'INT'),
      Person.value('@weight', 'INT'),
      Person.value('@xlink:href', 'NVARCHAR(500)'), -- THIS IS WHERE I NEED HELP
FROM
    @xml
    CROSS APPLY [MyXml].nodes('result/people')    AS MyXml(People)
    OUTER APPLY People.nodes('person')              AS People(Person);

Answer

Replace the xlink:href with xlinkhref

DECLARE @xml AS TABLE(MyXml xml);

INSERT into @xml values('<?xml version="1.0" encoding="UTF-8"?>
<result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xlink="http://www.w3.org/1999/xlink">
  <people lastUpdateDate="xxxx-xx-xx">
    <person name="Paul" surname="Who Cares" height="180" weight="89" xlink:href="https://i.stack.imgur.com/jMgYU.jpg?s=48&amp;g=1" />
  </people>
</result>')

UPDATE @xml set MyXml=REPLACE(CONVERT(NVARCHAR(MAX),(MyXml)),'xlink:href','xlinkhref')

SELECT
      Person.value('@name', 'NVARCHAR(50)'),
      Person.value('@surname', 'NVARCHAR(50)'),
      Person.value('@height', 'INT'),
      Person.value('@weight', 'INT'),
      Person.value('@xlinkhref', 'NVARCHAR(500)') -- THIS IS WHERE I NEED HELP
FROM
    @xml
    CROSS APPLY [MyXml].nodes('result/people')    AS MyXml(People)
    OUTER APPLY People.nodes('person')              AS People(Person);

Advertisement

Answer