Select nvarchar(max) xml fromat values from sql server

Question

I have the following XML:

<Attributes>
  <Map>
    <entry key="approvalSet">
      <value>
        <Approval>
          <ApprovalItem application="SP" id="9659102e" name="reporting_queue" operation="Add" />
          <ApprovalItem application="SP" id="1013b3f5" name="reporting_queue" operation="Add" />
          <ApprovalItem application="SP" id="09648f81" name="reporting_queue" operation="Add" />
        </Approval>
      </value>
    </entry>
    <entry key="DisplayName" value=" mike " />
    <entry key="Name" value="1222" />
    <entry key="policy" />
    <entry key="work">
      <value>
        <WorkLevel>Normal</WorkLevel>
      </value>
    </entry>
  </Map>
</Attributes>

I want to run a select sql that one of the column(attributes) has xml format(attached here). the sql is like:

SELECT 
    w.created,
    W.[attributes],
    W.[completer]
FROM [item_archive] W 
WHERE W.type='Manual'

How can I get the result of the records value from attributes column which has XML format as a new columns (application, id, name, and operation) values separably. The XML columns type is nvarchar((max),null).

Why are you storing XML as a `varchar` when you have a perfect datatype for the job: `xml`. You can't use XQUERY against a `varchar` either — Thom A, Dec 04 '18 at 22:24
[Here's a way](https://stackoverflow.com/a/18361423/6167855) with cross apply if you know the attribute names, or [another way](https://stackoverflow.com/a/3989477/6167855) if it was stored as XML — S3S, Dec 04 '18 at 22:25
Also, post your XML data as what it is `text`, not an image please. — Thom A, Dec 04 '18 at 22:25
the results columns would be : w.created, application, id, name,operation, w.[completer] — user2433850, Dec 04 '18 at 22:36
@user2433850 There's an "edit" button on your question. Please use that to add the information to your question. — Saaru Lindestøkke, Dec 04 '18 at 22:37

David Browne - Microsoft · Answer 1 · 2018-12-04T23:25:12.717

Like this:

    drop table if exists #item_archive 
    create table #item_archive
    ( 
      created datetime,
      attributes varchar(max) null,
      completer varchar(20),
      type varchar(20)
    )

    declare @d varchar(max) = '
<Attributes>
  <Map>
    <entry key="approvalSet">
      <value>
        <Approval>
          <ApprovalItem application="SP" id="9659102e" name="reporting_queue" operation="Add" />
          <ApprovalItem application="SP" id="1013b3f5" name="reporting_queue" operation="Add" />
          <ApprovalItem application="SP" id="09648f81" name="reporting_queue" operation="Add" />
        </Approval>
      </value>
    </entry>
    <entry key="DisplayName" value=" mike " />
    <entry key="Name" value="1222" />
    <entry key="policy" />
    <entry key="work">
      <value>
        <WorkLevel>Normal</WorkLevel>
      </value>
    </entry>
  </Map>
</Attributes>'

    insert into #item_archive(created,attributes,completer,type)
    values (getdate(),@d,'joe','Manual')

    go

    with W as 
    (
      select *, cast(attributes as xml) attributes_xml
      from #item_archive
    )
    select 
        w.created,
        w.completer,
        A.ApprovalItem.value('@application','varchar(20)') application,
        A.ApprovalItem.value('@id','varchar(20)') id,
        A.ApprovalItem.value('@name','varchar(50)') name,
        A.ApprovalItem.value('@operation','varchar(20)') operation
    from  W
    cross apply W.attributes_xml.nodes('/Attributes/Map/entry[@key="approvalSet"]/value/Approval/ApprovalItem') A(approvalItem)
    where W.type = 'Manual'

outputs

created                 completer            application          id                   name                                               operation
----------------------- -------------------- -------------------- -------------------- -------------------------------------------------- --------------------
2018-12-04 16:41:03.550 joe                  SP                   9659102e             reporting_queue                                    Add
2018-12-04 16:41:03.550 joe                  SP                   1013b3f5             reporting_queue                                    Add
2018-12-04 16:41:03.550 joe                  SP                   09648f81             reporting_queue                                    Add

(3 rows affected)

As I mentioned to my question the column type is not XML type is nvarchar((max),null). Your query does not working, I'm getting this error :The XMLDT method 'nodes' can only be invoked on columns of type xml — user2433850, Dec 04 '18 at 23:07
Still your edit code has .nodes which does not solve the error. — user2433850, Dec 05 '18 at 01:02
Can you explain? The sample code runs without error on my laptop. — David Browne - Microsoft, Dec 05 '18 at 01:39
In your edit code still has .nodes and when I run the sql getting the same error :The XMLDT method 'nodes' can only be invoked on columns of type xml. — user2433850, Dec 05 '18 at 13:51
It is invoked on a column of type xml. Check that you copied and pasted correctly. — David Browne - Microsoft, Dec 06 '18 at 00:32

Select nvarchar(max) xml fromat values from sql server

1 Answers1