Xbrl and Openxml()

[Former Member]

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

[Former Member]

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'

[Former Member]

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 *.

[Former Member]

Thank you!!!!