XML Parsing & T-SQL

Question

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.

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.

Bridge · Accepted Answer · 2014-02-18 15:31:24Z

5

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

edited Feb 18, 2014 at 15:31

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

answered Nov 4, 2013 at 19:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Waclawski Over a year ago

Marc, This is great stuff!!! I'm picking it apart as we speak. Now maybe what little hair I have will start growing back!! Thanks!!

Collectives™ on Stack Overflow

XML Parsing & T-SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related