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="http://www.xbrl.org/2003/linkbase">

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

</xbrli:xbrl>

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

SELECT *
FROM OPENXML ( @xml,
               './/*' )
     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

abon
46113
accept rate: 0%

edited 28 May '12, 16:08

Phil%20Mitchell's gravatar image

Phil Mitchell
1.9k1831


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="http://www.xbrl.org/2003/linkbase" 
            xmlns:link="http://www.xbrl.org/2003/linkbase"
            xmlns:xlink="http://www.xbrl.org/2003/linkbase" >
    <link:schemaRef xlink:type="simple" xlink:href="t-SARA-2011-03-31.xsd" />
</xbrli:xbrl>

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

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

The results of the query are as follows:

NULL,'simple','t-SARA-2011-03-31.xsd'
permanent link

answered 28 May '12, 16:05

Phil%20Mitchell's gravatar image

Phil Mitchell
1.9k1831
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×29
×22

question asked: 26 May '12, 15:15

question was seen: 3,273 times

last updated: 08 Jun '12, 08:12