Extract xml from another xml using OpenXML

Question

I have an xml, for example:

<AP>
  <value UserXML="<ADUsers><ADUser DisplayName = "User3 (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR" /></ADUsers>" SIDHash="ad8b3d173afa97b49b98c57b182356b6d0066f8c" Type="USER" DisplayName="User3 (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR" /> 
  <value UserXML="<ADUsers><ADUser DisplayName = "User4 (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR" /></ADUsers>" SIDHash="7a0600b592180dba051ec99f8b8b960515d3e05f" Type="USER" DisplayName="User4 (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR" /> 
</AP>

I have an xml (ADUsers inside my main xml AP)

I want to insert the ADUsers xml into a column in my sql database:

here is my query:

...
    DECLARE @InsertedAP AS TABLE (ID INT);


    DECLARE @docHandle INT
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @AP_List

    INSERT INTO
        AssociationPoints
        (APType,UserXML,SIDHash)
    OUTPUT INSERTED.AssociationPointID INTO @InsertedAP

    SELECT
        AP.[Type],
        CAST(AP.UserXML as XML),
        AP.SIDHash
    FROM
        OPENXML(@docHandle, 'AP/value', 1)
        WITH
            ([Type] CHAR(4), UserXML XML, SIDHash VARCHAR(50)) AP

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

...

but I get the error: Element-centric mapping must be used with OPENXML when one of the columns is of type XML.

why? how can I solve this issue? (The inner xml is sent encoded to the DB)

I attach script for example that can be run:

    declare @AP_List XML
    set @AP_List = '<AP><value UserXML="&lt;ADUsers&gt;&lt;ADUser DisplayName = &quot;User3   (rdserver\TEST\user3)&quot; Username=&quot;TEST\user3&quot; DomainID=&quot;43&quot; UserSID=&quot;ad8b3d173afa97b49b98c57b182356b6d0066f8c&quot; UserTypeCode=&quot;INTR&quot; /&gt;&lt;/ADUsers&gt;" SIDHash="ad8b3d173afa97b49b98c57b182356b6d0066f8c" Type="USER" DisplayName="User3   (rdserver\TEST\user3)" Username="TEST\user3" DomainID="43" UserSID="ad8b3d173afa97b49b98c57b182356b6d0066f8c" UserTypeCode="INTR"></value><value UserXML="&lt;ADUsers&gt;&lt;ADUser DisplayName = &quot;User4   (rdserver\TEST\user4)&quot; Username=&quot;TEST\user4&quot; DomainID=&quot;43&quot; UserSID=&quot;7a0600b592180dba051ec99f8b8b960515d3e05f&quot; UserTypeCode=&quot;INTR&quot; /&gt;&lt;/ADUsers&gt;" SIDHash="7a0600b592180dba051ec99f8b8b960515d3e05f" Type="USER" DisplayName="User4   (rdserver\TEST\user4)" Username="TEST\user4" DomainID="43" UserSID="7a0600b592180dba051ec99f8b8b960515d3e05f" UserTypeCode="INTR"></value></AP>'




        DECLARE @docHandle INT
EXEC sp_xml_preparedocument @docHandle OUTPUT, @AP_List

SELECT
    AP.[Type],
    CAST(AP.UserXML as XML),
    AP.SIDHash
FROM
    OPENXML(@docHandle, 'AP/value', 1)
    WITH
        ([Type] CHAR(4), UserXML XML, SIDHash VARCHAR(50)) AP

Because your attribute `UserXML` contains unescaped angle brackets and quotes. — Daniel Hilgarth, Oct 16 '13 at 09:22

score 0 · Answer 1 · answered Oct 19 '13 at 13:45

0

Can't say that I like your approach, but to get xml out ot xml you can use this query:

select 
    cast(T.C.value('@UserXML', 'nvarchar(max)') as xml) as UserXML
from @AP_List.nodes('AP/value') as T(C)

sql fiddle demo

answered Oct 19 '13 at 13:45

Roman Pekar

107,110
28
195
197

score 0 · Answer 2 · answered Oct 19 '13 at 14:54

Use NVARCHAR(MAX) in the schema declaration and cast to XML in the column list.

SELECT
    AP.[Type],
    CAST(AP.UserXML as XML) AS UserXML,
    AP.SIDHash
FROM
    OPENXML(@docHandle, 'AP/value', 1)
    WITH
        ([Type] CHAR(4), UserXML NVARCHAR(MAX), SIDHash VARCHAR(50)) AP

score 0 · Answer 3 · edited Dec 18 '13 at 13:02

0

Try to change OPENXML(@docHandle, 'AP/value', 1) to OPENXML(@docHandle, 'AP/value', 2)

edited Dec 18 '13 at 13:02

Mad Dog Tannen

7,129
5
31
55

answered Dec 18 '13 at 12:44

user3115290

1

Extract xml from another xml using OpenXML

3 Answers3