Post reply

Need Translation of XML Thread on different forum

  • October 14, 2008 at 6:28 am

    #103604

    Can anyone translate what this post means?

    http://www.windows-tech.info/15/4893ee57d33a3cb9.php

    I'm trying to import an XML reporting services extract into my local SQL Server, but as the post says, there is no namespace in the extract. And when I use XMLSpy on it, it won't validate because of the whole string with the report location in the header. I can't open up and edit the files, so replacing the namespace dynamically while processing the file would be a bonus. However, I can't understand the solution posted in the above thread.

    Here's the excerpt I'm concerned with:

    The switch from XML to nvarchar does not impact the namespace issue.... that is only a performance improvement.

    As to the prefix: You do not have to change the original document. All you have to know are the namespace uris that can appear inside your documents. Then you can write something like this:

    DECLARE @NsDoc nvarchar(max), @NsDecls nvarchar(max);

    declare @h INT;

    SET @NsDoc = N'&ltmydoc xmlns="urn1"&gt

    &ltsomens:somenselem xmlns:somens="urn2"&gt1&lt/somens:somenselem&gt

    &ltns:anothernselem xmlns:ns="urn3"&gt2&lt/ns:anothernselem&gt

    &ltanotherdefaultns xmlns="urn4"&gt3&lt/anotherdefaultns&gt

    &lt/mydoc&gt';

    SET @NsDecls = N'&ltnsdecls xmlns:ns1="urn1" xmlns:ns2="urn2" xmlns:ns3="urn3" xmlns:ns4="urn4"/&gt';

    EXECUTE sp_xml_preparedocument @h OUTPUT, @NsDoc, @NsDecls

    SELECT * FROM OPENXML (@h, '/ns1:mydoc',2)

    WITH ( somenselem int 'ns2:somenselem', anothernselem int 'ns3:anothernselem', anotherdefaultns int 'ns4:anotherdefaultns')

    EXECUTE sp_xml_removedocument @h

    Note that you can change the prefixes in your document that you want to process without having to change the prefixes that you use in @NsDecls and the queries.

    Not quite sure how this "replaces" the namespace. Any ideas?

    Thanks!

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • October 14, 2008 at 6:34 am

    #884566

    BTW, here's the header on the SSRS XML Extract:

    &lt?xml version="1.0"?&gt

    &ltReport xmlns="AHSRPT908Reinsure"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="AHSRPT908Reinsure http://reportserver?%

    2fAHSRPT908Reinsure&rs%3aFormat=XML&rc%

    3aSchema=True" Name="AHSRPT908Reinsure"&gt

    And I've hit hard carriage returns on the end of each line so it doesn't open up this thread too wide for the screen, so if you cut-n-paste it, make sure to remove those hard carriage returns.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply