t-sql with multiple XML namespaces in MS SQL-Server

Question

Good Day All,

This is similar to other namespace questions (e.g. StackOverflow Question) except I cannot seem to get my query to work.

I have a bunch of XML documents that I put in MS SQL Server I need to extract data from and I have no control over their format. I am having trouble stemming from what I believe is multiple namespaces.

Here is a sample.

DECLARE @table TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @table VALUES
(1, 
'<PyroSmartDO xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="PyroSmartDO_1_1.xsd" Major="0" Minor="0" Build="0" Revision="0">
  <Run Name="1-8-12" CreatedBy="user" CreatedDate="2012-08-02T10:39:33" Status="FINISHED" Type="SQA">
    <RunInfo>
      <WellInfos>
        <WellInfo WellNr="H12">
          <Sample CreatedDate="2012-08-14T16:44:27.765625+05:30">
            <Note>11- 0129</Note>
          </Sample>
        </WellInfo>
        <WellInfo WellNr="H11">
          <Sample CreatedDate="2012-08-14T16:44:27.765625+05:30">
            <Note>11- 0128</Note>
          </Sample>
        </WellInfo>
        <WellInfo WellNr="H10">
          <Sample CreatedDate="2012-08-14T16:44:27.765625+05:30">
            <Note>11- 0127</Note>
          </Sample>
        </WellInfo>
     </WellInfos>
    </RunInfo> 
  </Run>
 </PyroSmartDO>')


SELECT * FROM @table;

SELECT
    Content = XmlContent.value('(/PyroSmartDO/Run/RunInfo/WellInfos/WellInfo/Sample/Note)[1]', 'varchar(50)')

FROM @table
WHERE ID = 1

I am new to XML and MS SQL Server so some of these things are really fuzzy to me.

I get the correct XML document (the SELECT *) but I get instead of Content='11- 0129' I get Content=NULL. If I get rid of the xmlns="PyroSmartDO_1_1.xsd" the query works. I am really confused on namespaces and cannot seem to get the query to work with it in there.
I actually would like to get all the Samples (I only showed three, the real document has dozens of them)
I usually use CROSS APPLY traversing XML nodes, this query seems to navigate XML without it.

Thank you in advance !

Karl Kieninger · Accepted Answer · 2014-04-24 02:50:37Z

4

Since you don't have any control of the XML format you can mask your namespace issue by wildcarding with something like:

SELECT Content = XmlContent.value('(//*:Note)[1]', 'varchar(50)')
  FROM @table
 WHERE ID = 1

However, you will want to CROSS APPLY since you are going to want all the Note values rather than just [1].

For example:

SELECT Content = T.c.value('.', 'VARCHAR(50)')
  FROM @table
       CROSS APPLY 
       XmlContent.nodes('//*:Note') T(c)

edited Apr 24, 2014 at 2:50

answered Apr 23, 2014 at 18:46

Karl Kieninger

9,1692 gold badges36 silver badges49 bronze badges

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

4 Comments

user918967 Over a year ago

quite honestly I am not good at the CROSS APPLY how would that query work ?

Karl Kieninger Over a year ago

Ah, your "I usually use CROSS APPLY traversing XML nodes" statement led me to believe otherwise. I'll see if I can post in bit.

user918967 Over a year ago

Well I suppose I could have said - I figured out CROSS APPLY once a long time ago and if I monkey with it I break it. However, that never sounds as good ! Thanks !

Mitch Wheat Over a year ago

"mask your namespace issue by wildcarding" : awesome! got my vote!

Collectives™ on Stack Overflow

t-sql with multiple XML namespaces in MS SQL-Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related