CODEWITHSUNDEEP
sqlsql-serverxml
John Waclawski
John Waclawski

Reputation: 996

XML Parsing & T-SQL -- Part 2

The last of my XML Parsing & T-Sql questions for a while. I have an xml field with data such as below:

<Criminal xmlns="http://schemas.somewebpage.com/data/stuff">
  <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>
</Criminal>

This code does not seem to be working:

SELECT
Content = XmlContent.value('(/Criminal/MessageContent/Content)[1]', 'varchar(50)'),
Type = XmlContent.value('(/MessageContent/Type)[1]', 'varchar(50)')
FROM @table

...and only returns NULL.

Suggestions?

Upvotes: 1

Views: 188

Answers (1)

JohnD
JohnD

Reputation: 14787

The namespaces are tripping you up. You are also missing "/Criminal" in the 2nd part of the query. Try something like this:

;WITH XMLNAMESPACES(
  'http://schemas.somewebpage.com/data/stuff' as ns2,
  DEFAULT 'http://schemas.somewebpage.com/data/stuff'
  )
SELECT
Content = XmlContent.value('(/ns2:Criminal/MessageContent/Content)[1]', 'varchar(50)'),
Type = XmlContent.value('(/ns2:Criminal/MessageContent/Type)[1]', 'varchar(50)')
FROM @table

Results:

Content             Type
Content in here!!   Empty

Upvotes: 2

Related Questions