CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxquery
D_Tonthat
D_Tonthat

Reputation: 1

Extract XML nodes using OPENXML

sample_xml I tried to extract XML data using OPENXML in SQL, but the XML file contain prefixes such as: "pidx:CustomerID>01234", see sample_xml.

If I exclude the prefix "pidx:" it can't read data, if I include, error out:

Msg 6603, Level 16, State 2, Line 15 XML parsing error: Reference to undeclared namespace prefix: 'pidx'.

How do I do it?

Upvotes: 0

Views: 393

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Besides the fact, that you should never post your code / XML as a picture, there are some general hints:

  • Your prefixes are binding an element to a namespace.
  • This namespace must be declared!
  • If your xml (as posted in the picture) is complete, it is invalid! If it is just a portion of a bigger XML you'll find the namespace's declaration somewhere above, in most cases in the root element
  • OPENXML is outdated and should not be used any more. Better use the XML's native methods like .value or .nodes().

update

As requested in a comment some explanation about the need to declare a namespace. This XML is not valid:

<abc:test>1</abc:test>

Try this:

DECLARE @xml XML=N'<abc:test>1</abc:test>';

You'll get this

Msg 9459, Level 16, State 1, Line 1 XML parsing: line 1, character 10, undeclared prefix

Now declare the namespace and it works

DECLARE @xml XML=N'<abc:test xmlns:abc="blah">1</abc:test>';

Such a namespace is valid for the declaring node and all elements hierarchically below (=within).

In most cases namespaces are declared within a root node

Try this

DECLARE @xml XML=
N'
<root xmlns:abc="blah">
    <abc:test>1</abc:test>
</root>
';
SELECT @xml

Upvotes: 1

Related Questions