parsing xml for different xml structures

Question

Please can you give me some advice?

I need to parse this xml text into a readable format ie

Additional Notes: ???????????????????????????
Business Process ID: 30001

ETC

This will need to be done in transact sql on sql server 2014 in a view

I know I can write a script to pull out the data however the data ie Names & values change each time and the structure ie the nested xml (Questions) will be different each time.

Is there a way to find all the structure and then and then extract the data?

<AdapterItem xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://???????????????/??????????/2011-11-18/Data">
  <Attributes>
    <Attribute>
      <Name>Additional Notes</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">???????????????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Business Process ID</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">30001</Value>
    </Attribute>
    <Attribute>
      <Name>Channel Location</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Br??????????????????es</Value>
    </Attribute>
    <Attribute>
      <Name>Channel Sub Location</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Advisor desk</Value>
    </Attribute>
    <Attribute>
      <Name>Contact Reason</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Preferred method of contact</Value>
    </Attribute>
    <Attribute>
      <Name>Date Submitted</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:dateTime">2017-07-20T16:43:03.5090344+01:00</Value>
    </Attribute>
    <Attribute>
      <Name>Mobile Phone</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">???????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Service Request Language</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">English (United Kingdom)</Value>
    </Attribute>
    <Attribute>
      <Name>Service Request Type</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">??????????????????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Submission Channel</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Fa??????????????e</Value>
    </Attribute>
    <Attribute>
      <Name>Wish to Remain Anonymous</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">false</Value>
    </Attribute>
    <Attribute>
      <Name>Questions</Name>
        <Value i:type="AdapterItem">
          <Attributes>
            <Attribute>
              <Name>Contact Group</Name>
              <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:string">CUST</Value>
            </Attribute>
            <Attribute>
              <Name>Created</Name>
              <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:dateTime">2017-07-18T13:28:40.66</Value>
            </Attribute>
          </Attributes>
         </Value
    </Attribute>
    <Attribute>
      <Name>Agent Username</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">m????????e</Value>
    </Attribute>
    <Attribute>
      <Name>Start DateTime</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:dateTime">2017-07-20T16:41:43.833</Value>
    </Attribute>
    <Attribute>
      <Name>Customer</Name>
      <Value i:type="AdapterItem">
        <Attributes>
          <Attribute>
            <Name>Contact Group</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:string">CUST</Value>
          </Attribute>
          <Attribute>
            <Name>Created</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:dateTime">2017-07-18T13:28:40.66</Value>
          </Attribute>
          <Attribute>
            <Name>Created</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:String">jo b</Value>
          </Attribute>
        </Attributes>
      </Value
    </Attribute>
  </Attributes>
</AdapterItem>

Gottfried Lesigang · Accepted Answer · 2018-04-23 10:20:53Z

With the following approach you'll get the given XML shredded down to columns.

But: Your structure looks like it might be nested to an indefinit depth (a classical Bill of materials (or a BOM) in EAV-style).

The given approach reads two levels deep and you can easily add levels to extend this up to a known max level. If there is no max depth, you'd need a recursive approach...

Try it out:

DECLARE @xml XML=
N'<AdapterItem xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://???????????????/??????????/2011-11-18/Data">
  <Attributes>
    <Attribute>
      <Name>Additional Notes</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">???????????????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Business Process ID</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">30001</Value>
    </Attribute>
    <Attribute>
      <Name>Channel Location</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Br??????????????????es</Value>
    </Attribute>
    <Attribute>
      <Name>Channel Sub Location</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Advisor desk</Value>
    </Attribute>
    <Attribute>
      <Name>Contact Reason</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Preferred method of contact</Value>
    </Attribute>
    <Attribute>
      <Name>Date Submitted</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:dateTime">2017-07-20T16:43:03.5090344+01:00</Value>
    </Attribute>
    <Attribute>
      <Name>Mobile Phone</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">???????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Service Request Language</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">English (United Kingdom)</Value>
    </Attribute>
    <Attribute>
      <Name>Service Request Type</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">??????????????????????????????</Value>
    </Attribute>
    <Attribute>
      <Name>Submission Channel</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Fa??????????????e</Value>
    </Attribute>
    <Attribute>
      <Name>Wish to Remain Anonymous</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">false</Value>
    </Attribute>
    <Attribute>
      <Name>Questions</Name>
        <Value i:type="AdapterItem">
          <Attributes>
            <Attribute>
              <Name>Contact Group</Name>
              <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:string">CUST</Value>
            </Attribute>
            <Attribute>
              <Name>Created</Name>
              <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:dateTime">2017-07-18T13:28:40.66</Value>
            </Attribute>
          </Attributes>
         </Value>
    </Attribute>
    <Attribute>
      <Name>Agent Username</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">m????????e</Value>
    </Attribute>
    <Attribute>
      <Name>Start DateTime</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:dateTime">2017-07-20T16:41:43.833</Value>
    </Attribute>
    <Attribute>
      <Name>Customer</Name>
      <Value i:type="AdapterItem">
        <Attributes>
          <Attribute>
            <Name>Contact Group</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:string">CUST</Value>
          </Attribute>
          <Attribute>
            <Name>Created</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:dateTime">2017-07-18T13:28:40.66</Value>
          </Attribute>
          <Attribute>
            <Name>Created</Name>
            <Value xmlns:d7p1="http://www.w3.org/2001/XMLSchema" i:type="d7p1:String">jo b</Value>
          </Attribute>
        </Attributes>
      </Value>
    </Attribute>
  </Attributes>
</AdapterItem>';

--The query uses namespaces wildcards (*:), which is not recommended normally, but there are so many namespaces that it might be very erronous to declare them properly.
-- The query reads all <Attribute> nodes and OUTER APPLYs further <Attribute> nodes - if there are any...

SELECT A.TopAttr.value(N'(*:Name/text())[1]',N'nvarchar(max)') AS TopAttrName
      ,A.TopAttr.value(N'(*:Value/@*:type)[1]',N'nvarchar(max)') AS TopAttrType
      ,A.TopAttr.value(N'(*:Value/text())[1]',N'nvarchar(max)') AS TopAttrValue
      ,B.SecondLevel.value(N'(*:Name/text())[1]',N'nvarchar(max)') AS L2AttrName
      ,B.SecondLevel.value(N'(*:Value/@*:type)[1]',N'nvarchar(max)') AS L2AttrType
      ,B.SecondLevel.value(N'(*:Value/text())[1]',N'nvarchar(max)') AS L2AttrValue
FROM @xml.nodes(N'/*:AdapterItem/*:Attributes/*:Attribute') AS A(TopAttr)
OUTER APPLY A.TopAttr.nodes(N'*:Value/*:Attributes/*:Attribute') AS B(SecondLevel);

The result

+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| TopAttrName              | TopAttrType   | TopAttrValue                      | L2AttrName    | L2AttrType    | L2AttrValue            |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Additional Notes         | d4p1:string   | ???????????????????????????       | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Business Process ID      | d4p1:string   | 30001                             | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Channel Location         | d4p1:string   | Br??????????????????es            | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Channel Sub Location     | d4p1:string   | Advisor desk                      | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Contact Reason           | d4p1:string   | Preferred method of contact       | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Date Submitted           | d4p1:dateTime | 2017-07-20T16:43:03.5090344+01:00 | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Mobile Phone             | d4p1:string   | ???????????????????               | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Service Request Language | d4p1:string   | English (United Kingdom)          | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Service Request Type     | d4p1:string   | ??????????????????????????????    | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Submission Channel       | d4p1:string   | Fa??????????????e                 | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Wish to Remain Anonymous | d4p1:string   | false                             | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Questions                | AdapterItem   | NULL                              | Contact Group | d7p1:string   | CUST                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Questions                | AdapterItem   | NULL                              | Created       | d7p1:dateTime | 2017-07-18T13:28:40.66 |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Agent Username           | d4p1:string   | m????????e                        | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Start DateTime           | d4p1:dateTime | 2017-07-20T16:41:43.833           | NULL          | NULL          | NULL                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Customer                 | AdapterItem   | NULL                              | Contact Group | d7p1:string   | CUST                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Customer                 | AdapterItem   | NULL                              | Created       | d7p1:dateTime | 2017-07-18T13:28:40.66 |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+
| Customer                 | AdapterItem   | NULL                              | Created       | d7p1:String   | jo b                   |
+--------------------------+---------------+-----------------------------------+---------------+---------------+------------------------+

UPDATE: recursive Approach

Try this for an indifint depth:

WITH recCTE AS
(
    SELECT 1 AS NestLevel
          ,CAST(NULL AS UNIQUEIDENTIFIER) AS ParentAttribute
          ,NEWID() AS AttributeKey
          ,A.TopAttr.value(N'(*:Name/text())[1]',N'nvarchar(max)') AS AttrName
          ,A.TopAttr.value(N'(*:Value/@*:type)[1]',N'nvarchar(max)') AS AttrType
          ,A.TopAttr.value(N'(*:Value/text())[1]',N'nvarchar(max)') AS AttrValue
          ,A.TopAttr.query(N'*:Value/*:Attributes/*:Attribute') AS NextLevel
    FROM @xml.nodes(N'/*:AdapterItem/*:Attributes/*:Attribute') AS A(TopAttr)

    UNION ALL

    SELECT r.NestLevel+1
          ,r.AttributeKey
          ,NEWID()
          ,B.NextAttr.value(N'(*:Name/text())[1]',N'nvarchar(max)') 
          ,B.NextAttr.value(N'(*:Value/@*:type)[1]',N'nvarchar(max)') 
          ,B.NextAttr.value(N'(*:Value/text())[1]',N'nvarchar(max)') 
          ,B.NextAttr.query(N'*:Value/*:Attributes/*:Attribute') 
    FROM recCTE AS r
    OUTER APPLY r.NextLevel.nodes(N'*:Attribute') AS B(NextAttr)
    WHERE NextLevel.exist('*:Attribute')=1
)
SELECT * FROM recCTE;

The query starts with the level-one-attributes as anchor and passes - if there are any - lower levels as query(), which is a fragment of deeper nodes. The recursive part will traverse this down, as long as there are more attributes.

The GUIDs are applied to keep the parent-child relation.

Thanks v much, you are correct in your suspicions and this will need to be recursive as I don't know the levels. Each on could be potentially different. I can run the top level and then each item the has a type of AdaptorItem I can then query in a cursor. Im trying to figure out how to query a specific level down.ie A.TopAttr.value(N'(*:Value/text())[1]',N'nvarchar(max)') AS TopAttrValue FROM @data.nodes(N'/*:AdapterItem/*:Attributes/*:Attribute[Name = ''Customer'']/*:Attributes/*:Attribute') AS A(TopAttr);

Collectives™ on Stack Overflow

parsing xml for different xml structures

1 Answer 1

UPDATE: recursive Approach

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE: recursive Approach

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related