The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I got a problem with a xbrl file. Let's say the file content is this:

<?xml version="1.0" encoding="UTF-8" ?>
<xbrli:xbrl xmlns:link="">

<link:schemaRef xlink:type="simple" xlink:href="t-SARA-2011-03-31.xsd" />


I wanna be able to use the openxml command for reading the values of xlink:type and xlink:href. I tried but no solution in sight. Using xp_read_file and openxml I should have something like (let's skip the INSERT and SELECT parts):

               './/*' )
     WITH ( schemaRef CHAR (50)  'text()',
            type  CHAR (50)  '@mp:xmltext',
            href CHAR (50)  '@mp:localname');

What I should write in place of the two @mp:...? What I do wrong?

Thank you for your help

asked 26 May '12, 15:15

abon's gravatar image

accept rate: 0%

edited 28 May '12, 16:08

Phil%20Mitchell's gravatar image

Phil Mitchell

I'm not entirely certain what you actually want to get out of this, but I'll take my best guess. With some trial-and-error I did notice that the XML parser doesn't like your XML due to missing namespace declarations. If I add the missing namespace declarations (using a dummy value for the URL), I create the following XML:

<?xml version="1.0" encoding="UTF-8" ?>
<xbrli:xbrl xmlns:xbrli="" 
            xmlns:xlink="" >
    <link:schemaRef xlink:type="simple" xlink:href="t-SARA-2011-03-31.xsd" />

Once I have that, I can alter your query slightly to pick out the type and href attributes from the schemaRef tag:

              './/*:schemaRef' )
     WITH ( schemaRef CHAR (50) 'text()',
            type      CHAR (50) '@*:type',
            href      CHAR (50) '@*:href' );

The results of the query are as follows:

permanent link

answered 28 May '12, 16:05

Phil%20Mitchell's gravatar image

Phil Mitchell
accept rate: 27%

edited 28 May '12, 16:06

Note that the *: in front of schemaRef, type, and href all refer to the namespace. i.e., in "any" namespace, choose these tag names. I tried putting the namespace in explicitly, but it complained that I hadn't defined the namespace. It's unclear to me how to get it to work without the *.

(29 May '12, 09:36) Phil Mitchell

Thank you!!!!

(08 Jun '12, 08:01) abon
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 26 May '12, 15:15

question was seen: 1,168 times

last updated: 08 Jun '12, 08:12