XML Column

• mceventphoto

  Ten Centuries

  Points: 1299

  More actions

  July 27, 2017 at 1:54 pm

  #327696

  How can I determine what is stored in a XML column? Is there a way I can query the column to determine what tags, attributes, values, etc, etc are stored in the XML? How do I find out the XML structure?

  Thanks,
  MC

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 28, 2017 at 2:02 am

  #1953148

  I'm not 100%  sure what you want here, but you can query XML by using XQuery in T-SQL.

  Without any sample data, or expected output, there's not a lot more I can tell you than give you a sample query myself:
  CREATE TABLE #XML (XMLColumn xml);
GO

  INSERT INTO #XML
SELECT
'<customer>
    <name>Mr Smith</name>
    <sale id="1">
      <item name="Cheese" value="4.95"/>
      <item name="Bread" value="1.99"/>
      <date>20170726</date>
    </sale>
    <sale id="2">
      <item name="Eggs" value="6.99"/>
      <item name="Beans" value="0.87"/>
      <date>20170727</date>
    </sale>
</customer>';
GO

  SELECT *
FROM #XML;
GO
SELECT X.XMLColumn.value('(customer/name/text())[1]','varchar(50)') AS CustomerName,
       c.s.value('@id', 'int') AS SaleID,
       s.i.value('@name', 'varchar(50)') AS ItemName,
       s.i.value('@value', 'varchar(50)') AS ItemValue,
       CAST(c.s.value('(date/text())[1]','varchar(50)') AS date) AS SaleDate
FROM #XML X
    CROSS APPLY X.XMLColumn.nodes('/customer/sale') c(s)
    CROSS APPLY c.s.nodes('item') s(i);

  GO
DROP TABLE #XML;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 28, 2017 at 2:49 am

  #1953162

  You can do this get all of the element and attribute names

  SELECT DISTINCT c.s.value('local-name(.)','VARCHAR(64)') AS ElementName
FROM #XML x
CROSS APPLY x.XMLColumn.nodes('//*') AS c(s);

  SELECT DISTINCT c.s.value('local-name(.)','VARCHAR(64)') AS AttributeName
FROM #XML x
CROSS APPLY x.XMLColumn.nodes('//@*') AS c(s);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537