Parse an XML value with a colon in SQL Server

Question

I need help to parse XML in SQL Server. I need to get "d1p1:Val2" value and concatenation of values for "d2p1:string".

<FirstData xmlns:i="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:d1p1="http://XXXXXX" xmlns="http://YYYYYY" i:type="d1p1:StaticInfo">
<Timestamp>0</Timestamp>
<ActionResult i:nil="true" />
<d1p1:Val1 xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d2p1:string>1</d2p1:string>
<d2p1:string>2</d2p1:string>
<d2p1:string>3</d2p1:string>
<d2p1:string>4</d2p1:string>
</d1p1:Val1>
<d1p1:Val2>false</d1p1:Val2>
</FirstData>

possible dupe: stackoverflow.com/questions/15680259/parse-xml-in-sql-server — sniperd
– sniperd, Commented Apr 2, 2018 at 15:05

Gottfried Lesigang · Accepted Answer · 2018-04-03 08:30:49Z

0

Your question is not very clear, but this might help you:

DECLARE @xml XML=
N'<FirstData xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:d1p1="http://XXXXXX" xmlns="http://YYYYYY" i:type="d1p1:StaticInfo">
  <Timestamp>0</Timestamp>
  <ActionResult i:nil="true" />
  <d1p1:Val1 xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:string>1</d2p1:string>
    <d2p1:string>2</d2p1:string>
    <d2p1:string>3</d2p1:string>
    <d2p1:string>4</d2p1:string>
  </d1p1:Val1>
  <d1p1:Val2>false</d1p1:Val2>
</FirstData>';

WITH XMLNAMESPACES(DEFAULT 'http://YYYYYY'
                          ,'http://XXXXXX' AS d1p1
                          ,'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS d2p1)
SELECT @xml.value(N'(/FirstData/d1p1:Val2/text())[1]','bit') AS D1P1_Val2
      ,@xml.query(N'data(/FirstData/d1p1:Val1/d2p1:string/text())').value(N'text()[1]',N'nvarchar(max)') AS AllStrings;

The result

D1P1_Val2   AllStrings
0           1 2 3 4

This is the - not recommended - minimal query:

SELECT @xml.value(N'(//*:Val2)[1]','bit') AS D1P1_Val2
      ,@xml.query(N'data(//*:string)').value(N'.',N'nvarchar(max)') AS AllStrings;

answered Apr 3, 2018 at 8:30

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

1 Comment

Bohdan Over a year ago

is it possible to add a delimiter when concatenating?

Gilles Quénot · Accepted Answer · 2018-04-02 14:36:45Z

0

Try something like this (can't test it myself ) :

SELECT Instructions.query('
     declare namespace d1p1="http://XXXXXX";
     declare namespace d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays";
        concat(//d1p1:Val2, " ", //d2p1:string[1]);
    ')

I think you just have to elaborate a bit on this

answered Apr 2, 2018 at 14:36

Gilles Quénot

188k43 gold badges232 silver badges229 bronze badges

3 Comments

Bohdan Over a year ago

An error: "XQuery [query()]: No more tokens expected at the end of the XQuery expression. Found ';'."

Gilles Quénot Over a year ago

I never said it's a full solution, just a start

Michael Kay Over a year ago

As the error message says, the final ";" in the query should be removed.

Collectives™ on Stack Overflow

Parse an XML value with a colon in SQL Server

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related