none
reading xml data RRS feed

  • Question

  • I have many similar tables with many common fields but each also has some unique fields

    I figure to do a "select * from table for xml raw" for each table into a variable, then for each table and then insert the variable into a table with one xml column.

    i get a set of rows for each row in the table like <row col1 = "X" col2 ="Y" col3 ="z"... /> which is fine.

    now I have the data in the table. I want to retrieve all column col2's from the table's xml

    can you please give me the code to extract col2 from the table containing the xmls

    thanx

    david


    ???

    Wednesday, August 21, 2019 7:22 AM

Answers

  • Hi David,

    With no DDL and sample data population provided I am sticking with my examples.

    I slightly modified attributes based XML sample to answer your questions ##1,2

    Please try it in SSMS:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT PRIMARY KEY IDENTITY(1,1), xmlData XML);
    INSERT INTO @tbl
    VALUES ('<row col1 = "X" col2 ="Y1" col3 ="z"/>')
    , ('<row col1 = "X" col2 ="Y2" col3 ="z"/>')
    , ('<row col1 = "wow" col2 ="Y3" col3 ="z"/>');
    -- DDL and sample data population, end
    
    SELECT * FROM @tbl;
    
    -- retrieve as a rectangular data set
    SELECT ID
    	, col.value('@col1','VARCHAR(100)') AS [col1]
    	, col.value('@col2','VARCHAR(100)') AS [col2]
    	, col.value('@col3','VARCHAR(100)') AS [col3]
    FROM @tbl AS tbl
        CROSS APPLY tbl.[xmlData].nodes('/row') AS tab(col);
    
    -- retrieve as a rectangular data set
    -- simulate WHERE clause
    SELECT ID
    	, col.value('@col1','VARCHAR(100)') AS [col1]
    	, col.value('@col2','VARCHAR(100)') AS [col2]
    	, col.value('@col3','VARCHAR(100)') AS [col3]
    FROM @tbl AS tbl
        CROSS APPLY tbl.[xmlData].nodes('/row[@col2="Y3"]') AS tab(col);
    • Marked as answer by berli Sunday, September 8, 2019 2:19 PM
    Thursday, August 22, 2019 2:04 PM

All replies

  • I am also going to need a select all rows from the table' xml where col2="x"

    thanx


    ???

    Wednesday, August 21, 2019 7:25 AM
  • I also think I will need an index on col2. what would be the syntax to create a select xml index

    thanx again


    ???

    Wednesday, August 21, 2019 7:28 AM
  • Hi David,

    To help you, please provide a DDL and sample data population.

    Along the following example:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY,[xmlData] XML NOT NULL);
    
    INSERT INTO @tbl([xmlData])
    VALUES
    (N'<root>
       <row id="1">
          <city>Miami</city>
          <populaton>470914</populaton>
          <submitted>2019-08-12</submitted>
       </row>
       <row id="2">
          <city>Orlando</city>
          <populaton>285713</populaton>
          <submitted>2019-08-10</submitted>
       </row>
    </root>')
    , (N'<root>
       <row id="10">
          <city>Tampa</city>
          <populaton>392890</populaton>
          <submitted>2019-07-25</submitted>
       </row>
    </root>')
    -- DDL and sample data population, end
    Wednesday, August 21, 2019 1:44 PM
  • Hi David,

    While I am waiting for your reply, here is another example for you.

    -- DDL and sample data population, start DECLARE @tbl TABLE (ID INT PRIMARY KEY IDENTITY(1,1), xmlData XML); INSERT INTO @tbl VALUES ('<row col1 = "X" col2 ="Y1" col3 ="z"/>') , ('<row col1 = "X" col2 ="Y2" col3 ="z"/>') , ('<row col1 = "wow" col2 ="Y3" col3 ="z"/>'); -- DDL and sample data population, end

    SELECT ID , col.value('(.)','VARCHAR(100)') AS [col2] FROM @tbl AS tbl CROSS APPLY tbl.[xmlData].nodes('/row/@col2') AS tab(col); SELECT ID , col.value('(.)','VARCHAR(100)') AS [col1] FROM @tbl AS tbl CROSS APPLY tbl.[xmlData].nodes('/row[@col2="Y3"]/@col1') AS tab(col);

    Wednesday, August 21, 2019 2:55 PM
  • I guess I wasn't clear, based on your response.

    I didn't provide a specific example as the request is very general.

    create the data via "select * from table for xml raw" for any table you like. this turns the table's data into an xml in the form of <row col1="1" col2=""/>

    now

    1. I want to effectively undo and select from this  xml. how do I do that? ie select * from xml and get the data in a tabular format as if I had done a select * from the original table

    2. I want to be able to select only some of the data from the xml i.e. add "where some-xml-column = a value". how do I do that? ie select * from xml where col1=value just as if I had written select * from table where col1=value

    3. I am expecting to end up with a large table. I want to index this xml. how do I do that? ie create index X on xml(col1) just as if I had create index on table(col1) 

    I have found some examples for xml path, but I am asking about xml raw as it looks to be significantly smaller (about half the length) as there are far fewer <tag></tag>

    i.e as set of

    <row col1="1" col2="Address1" />

    <row col1="2" col2="Address2" />

    instead of

    <row>
      <col1>1</col1>
      <col2>Address1</col2>
    </row>
    <row>
      <col1>2</col1>
      <col2>Address2</col2>
    </row>

    thanx

    david


    ???

    Thursday, August 22, 2019 6:14 AM
  • Hi David,

    With no DDL and sample data population provided I am sticking with my examples.

    I slightly modified attributes based XML sample to answer your questions ##1,2

    Please try it in SSMS:

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT PRIMARY KEY IDENTITY(1,1), xmlData XML);
    INSERT INTO @tbl
    VALUES ('<row col1 = "X" col2 ="Y1" col3 ="z"/>')
    , ('<row col1 = "X" col2 ="Y2" col3 ="z"/>')
    , ('<row col1 = "wow" col2 ="Y3" col3 ="z"/>');
    -- DDL and sample data population, end
    
    SELECT * FROM @tbl;
    
    -- retrieve as a rectangular data set
    SELECT ID
    	, col.value('@col1','VARCHAR(100)') AS [col1]
    	, col.value('@col2','VARCHAR(100)') AS [col2]
    	, col.value('@col3','VARCHAR(100)') AS [col3]
    FROM @tbl AS tbl
        CROSS APPLY tbl.[xmlData].nodes('/row') AS tab(col);
    
    -- retrieve as a rectangular data set
    -- simulate WHERE clause
    SELECT ID
    	, col.value('@col1','VARCHAR(100)') AS [col1]
    	, col.value('@col2','VARCHAR(100)') AS [col2]
    	, col.value('@col3','VARCHAR(100)') AS [col3]
    FROM @tbl AS tbl
        CROSS APPLY tbl.[xmlData].nodes('/row[@col2="Y3"]') AS tab(col);
    • Marked as answer by berli Sunday, September 8, 2019 2:19 PM
    Thursday, August 22, 2019 2:04 PM
  • that seems to work. how do I index specific tags in the xml?

    ???

    Thursday, August 22, 2019 2:11 PM
  • Hi David,

    Glad to hear that you are making some progress.


    Thursday, August 22, 2019 2:18 PM
  • i am going home for the weekend in a few minutes. all I really need is to be able to make an index on a few specific column(s). the full xml index will be huge and I don't need every tag indexed

    assume a million xml rows in the table. each one has a dozen tags out of a possible 100 tags. most orws have a few key value tags which I want to search.

    ie show me all rows which have the tag key1 with value =val1. i won't see them if they dont have this tag at all and obviously i also wont see them if the key is some other value. I definitely want selective indexing


    ???

    Thursday, August 22, 2019 2:30 PM
  • Hi David,

    Indexing is DDL dependent.

    I provided you 3 links on XML indexing in my previous reply.

    It will give you a good head start.

    Thursday, August 22, 2019 2:34 PM