XML Parsing & T-SQL -- Part 2

Question

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?

does it work if you remove the xml namespace attributes in the xml? — Jay
– Jay, Commented Nov 6, 2013 at 14:18
I removed the :xsi and :xsd namespace (if that was what you were referring to) and got an error. Then I removed all namespace references all together till was only left with: Criminal> <MessageContent> <Content>Content in here!!</Content> <Type>Empty</Type> </MessageContent> </Criminal> and my select statement worked. Only when the use of 3 namespaces did it cause problems for me. — John Waclawski
– John Waclawski, Commented Nov 6, 2013 at 14:53

JohnD · Accepted Answer · 2013-11-06 15:28:36Z

2

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

edited Nov 6, 2013 at 15:28

answered Nov 6, 2013 at 14:39

JohnD

14.9k4 gold badges42 silver badges54 bronze badges

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

1 Comment

John Waclawski Over a year ago

That did it! Must remember to declare the namespaces.

Collectives™ on Stack Overflow

XML Parsing & T-SQL -- Part 2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related