0

I got stuck in SQL - can anyone please help?

I have a XML shown below; I want to read the XML tags and create a table in SQL Server.

I tried the query also shown below, but that did not fetch the data. The result set is empty. I have used cross apply and cast to xml.

Apparently I can write XPath queries to select tags into columns, which is what I want, except there is like 1000 possible tag names, and I don't want to write them all out (and possibly miss one) and hence i am looking for solution that we read xml tags and generate table with values and tags in SQL.

SELECT 
    t.ID,
    T0.Color.value('local-name(.)', 'nvarchar(max)') AS TagName,
    T0.Color.value('(@name)[1]','nvarchar(max)') AS EntityName,
    T0.Color.value('text()[1]','nvarchar(max)') AS TagValue  
FROM
    ConfigurationDnaItem t
CROSS APPLY 
    (SELECT 
         CAST(t.Value AS XML)) AS A(AllNodes)
CROSS APPLY 
    A.AllNodes.nodes('/hierarchy/nodes/list-entity') AS T0(Color)
WHERE 
    id = '1D09BFCB-AE0A-4346-835D-4BBCF2BBB503'

Output Required

Please see the above table structure I am looking for remove from selection N/A the last row that would be much better.

Thanks for help.

<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGPaintH" xmlns="urn:guru.cincom.com-Hierarchy">
    <nodes>
        <list-entity name="HubPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DP]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Hubs]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DP]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="DoorPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.DTT6]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.DTT6]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[GELCOAT WHITE]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DS]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Door]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DS]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="UnderPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DM]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Under]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DM]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="PaintFleetMasterBoxesList">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="HasNSO" type="boolean">false</characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DS]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Boxes]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="RadiusGuardPaint">
          <values>
            <value>
              <key-value><![CDATA[N/A]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[N/A]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Guard Colour not applicable]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">1</characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Radius Guard Colour]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DS]]></extended-property>
          </extended-properties>
        </list-entity>
    </nodes>
</hierarchy>
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
khushal shah
  • 5
  • 2
  • 1
    The query you have given does not generate that resultset, please elaborate on what exact results you want. Please show as *text* not *images* – Charlieface Jan 21 '22 at 03:14
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. – Yitzhak Khabinsky Jan 21 '22 at 03:19
  • It looks like you *might* have wanted something like this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=204c2eb1a82b2966b41655dedd1a2763 but without expected output it's very hard to say. You should also supply the sample data as proper `CREATE TABLE` and `INSERT`, as mentioned – Charlieface Jan 21 '22 at 03:27

1 Answers1

0

It's rather unclear what your expected output is, but it looks like you want something like this

WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
      ,T0.Color.value('(extended-properties/extended-property[@name = "ALTNAME"]/text())[1]', 'nvarchar(max)') AS TagName
      ,T0.Color.value('@name','nvarchar(max)') AS EntityName
      ,T0.Color.value('(values/value/key-value/text())[1]','nvarchar(max)') AS TagValue  
FROM ConfigurationDnaItem t
CROSS APPLY (SELECT CAST(t.Value AS XML)) As A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/list-entity[not(values/value/key-value/text() = "N/A")]') AS T0(Color)
where t.id = '1D09BFCB-AE0A-4346-835D-4BBCF2BBB503';

There were a number of issues with your existing query:

  • You are missing a namespace declaration.
  • local-name(.) would get you the node name list-entity, it's unclear why you would have wanted that.
  • Some of the data that you seem to want is stored in extended-properties/extended-property which you need to filter by attribute name, then pull out the inner text.
  • @name gets you the name attribute, which is not present in your image. I have left it in however. It does not require [1].
  • text()[1] would not get you anything, as the context node does not have any direct inner text nodes. Instead, it seems you wanted to descend to values/value/key-value/text().
  • To filter out the N/A value, you need the following predicate [not(values/value/key-value/text() = "N/A")]

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • you are genius. your solution worked for me. you are amazing. instead of few unclear statements were present but you have resolved my issue. Thanks heaps. This is my 1st post here and you clear my doubt in few minutes. Thanks – khushal shah Jan 21 '22 at 03:48
  • Just want to know- how to select ( <![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]>) in result set? I tried this - ,T0.Color.value('(characteristics/characteristic[@name = "Description"]/text())[1]', 'nvarchar(max)') AS ColorDescription – khushal shah Jan 21 '22 at 04:04
  • Which bit of the above do you actually want? The inner text? You really need to be much clearer about the result you want. Perhaps you need `T0.Color.value('(values/value/characteristics/characteristic[@name = "Description"]/text())[1]', 'nvarchar(100)')` – Charlieface Jan 21 '22 at 04:06
  • Yes, Inner text example - Fleet Red(PPG Code: AFL.9H1B) – khushal shah Jan 21 '22 at 04:11
  • https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3e6dfdc965590ef67946dc98a9b5565e You get the idea – Charlieface Jan 21 '22 at 04:12
  • How to filter out (Nil, NA and N/A all together in one statement? We have added filter for N/A above CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/list-entity[not(values/value/key-value/text() = "N/A")]') AS T0(Color) – khushal shah Jan 21 '22 at 04:41
  • `not(values/value/key-value[text() = "N/A" or text() = "NA" or text() = "Nil"])` maybe? You really need to learn how to do this yourself – Charlieface Jan 21 '22 at 04:55
  • Thanks for helping again this will help me to learn sql query. It worked. – khushal shah Jan 23 '22 at 21:41