XML Parsing & T-SQL

Given the following from an XML field in a table:

    <View>
      <Criminal xmlns="http://tempuri.org/crimes.xsd">
        <Person>
          <PersonID>1234</PersonID>
          <LastName>SMITH</LastName>
          <FirstName>KEVIN</FirstName>
        <Cases>
          <PersonID>1234</PersonID>
          <CaseNumber>12CASE34</CaseNumber>
        </Cases>
       </Person>
      </Criminal>
     </View>

How would I pull the Person/PersonID, LastName, Firstname info? Same goes for the CaseNumber.

  • Automating data import from excel sheet into SQL server using python
  • Identify a specific sequence of records in a table
  • Difference between dates when grouping in SQL
  • Database removal of old entries
  • SQL: create new record based on several ones
  • How to select the next top rows including the new rows that User added between the selection.
  • My next issue is similar to above but lets add a second namespace:

    <MessageContent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <Content>Content in here!!</Content>
       <Type>Empty</Type>
    </MessageContent>
    

    Notice I have 2 namespaces in there AND they also have “:xsi” and “:xsd” in there too. I think those are referred to as schemas.

  • Blank screen when looking at Activity Monitor details
  • SQL Server 2005 Issue Column name or number of supplied values does not match table definition
  • SQL function to replicate SUMIF in my Excel
  • How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?
  • Trouble with jQuery MaskMoney plugin and Field type Decimal (SQL SERVER table)
  • Insert trigger not inserting new rows in other table
  • One Solution collect form web for “XML Parsing & T-SQL”

    Try this:

    DECLARE @table TABLE (ID INT NOT NULL, XmlContent XML)
    
    INSERT INTO @table VALUES(1, '<View>
          <Criminal xmlns="http://tempuri.org/crimes.xsd">
            <Person>
              <PersonID>1234</PersonID>
              <LastName>SMITH</LastName>
              <FirstName>KEVIN</FirstName>
            <Cases>
              <PersonID>1234</PersonID>
              <CaseNumber>12CASE34</CaseNumber>
            </Cases>
           </Person>
          </Criminal>
         </View>')
    
    ;WITH XMLNAMESPACES('http://tempuri.org/crimes.xsd' AS ns)
        SELECT
            PersonID = XmlContent.value('(/View/ns:Criminal/ns:Person/ns:PersonID)[1]', 'int'),
            FirstName = XmlContent.value('(/View/ns:Criminal/ns:Person/ns:FirstName)[1]', 'varchar(50)'),
            LastName = XmlContent.value('(/View/ns:Criminal/ns:Person/ns:LastName)[1]', 'varchar(50)')
        FROM @table
        WHERE ID = 1
    

    Returns an output of:

    enter image description here

    And for your second part of the question: yes, you have two namespaces defined – but they’re not being used at all – so you can basically just ignore them:

    INSERT INTO @table VALUES(2, '<MessageContent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <Content>Content in here!!</Content>
       <Type>Empty</Type>
    </MessageContent>')
    
    SELECT
        Content = XmlContent.value('(/MessageContent/Content)[1]', 'varchar(50)'),
        Type = XmlContent.value('(/MessageContent/Type)[1]', 'varchar(50)')
    FROM @table
    WHERE ID = 2
    

    Returns:

    enter image description here

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.