Parse an XML value with a colon in SQL Server

• duhast2012

  SSC Veteran

  Points: 230

  More actions

  April 2, 2018 at 8:33 am

  #151604

  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>

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  April 5, 2018 at 1:36 am

  #1985591

  duhast2012 - Monday, April 2, 2018 8:33 AM

  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>

  Here is one way of doing it, should get you passed this hurdle.
  😎
  Study this solution and make certain that your sample data truly reflects the problem.

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TXML XML = '<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://schemas.microsoft.com/2003/10/Serialization/Arrays')
,VAL_NODES AS
(
  SELECT
   ROW_NUMBER() OVER (ORDER BY @@VERSION)   AS ELM_RID
   ,TN.DATA.query('.')         AS ELM_XML
   ,TN.DATA.value('local-name(.)','VARCHAR(50)') AS ELM_NAME
   ,TN.DATA.value('(./text())[1]','VARCHAR(50)') AS ELM_STR
  FROM  @TXML.nodes('//*') TN(DATA)
  WHERE TN.DATA.value('local-name(.)','VARCHAR(50)') LIKE 'Val%'
)
,NODES_DATA AS
(
  SELECT
   VN.ELM_RID
   ,VN.ELM_NAME
   ,VN.ELM_STR
   ,NSTR.DATA.value('(./text())[1]','VARCHAR(50)') AS NSTR_VAL
  FROM   VAL_NODES VN
  OUTER APPLY VN.ELM_XML.nodes('//string') NSTR(DATA)
)
SELECT
  ND.ELM_STR
 ,(
   SELECT
    '' + SND.NSTR_VAL
   FROM  NODES_DATA  SND
   WHERE SND.ELM_NAME = 'Val1'
   FOR XML PATH(''), TYPE
  ).value('(./text())[1]','VARCHAR(50)') AS STRING_VALUE
FROM  NODES_DATA  ND
WHERE ND.ELM_STR IS NOT NULL;

  Output
  
ELM_STR  STRING_VALUE
false        1234


• duhast2012

  SSC Veteran

  Points: 230

  More actions

  April 5, 2018 at 7:15 am

  #1985659

  Eirikur Eiriksson - Thursday, April 5, 2018 1:36 AM

  duhast2012 - Monday, April 2, 2018 8:33 AM

  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>

  Here is one way of doing it, should get you passed this hurdle.
  😎
  Study this solution and make certain that your sample data truly reflects the problem.

  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TXML XML = '<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://schemas.microsoft.com/2003/10/Serialization/Arrays')
,VAL_NODES AS
(
  SELECT
   ROW_NUMBER() OVER (ORDER BY @@VERSION)   AS ELM_RID
   ,TN.DATA.query('.')         AS ELM_XML
   ,TN.DATA.value('local-name(.)','VARCHAR(50)') AS ELM_NAME
   ,TN.DATA.value('(./text())[1]','VARCHAR(50)') AS ELM_STR
  FROM  @TXML.nodes('//*') TN(DATA)
  WHERE TN.DATA.value('local-name(.)','VARCHAR(50)') LIKE 'Val%'
)
,NODES_DATA AS
(
  SELECT
   VN.ELM_RID
   ,VN.ELM_NAME
   ,VN.ELM_STR
   ,NSTR.DATA.value('(./text())[1]','VARCHAR(50)') AS NSTR_VAL
  FROM   VAL_NODES VN
  OUTER APPLY VN.ELM_XML.nodes('//string') NSTR(DATA)
)
SELECT
  ND.ELM_STR
 ,(
   SELECT
    '' + SND.NSTR_VAL
   FROM  NODES_DATA  SND
   WHERE SND.ELM_NAME = 'Val1'
   FOR XML PATH(''), TYPE
  ).value('(./text())[1]','VARCHAR(50)') AS STRING_VALUE
FROM  NODES_DATA  ND
WHERE ND.ELM_STR IS NOT NULL;

  Output
  
ELM_STR  STRING_VALUE
false        1234


  Thank you!!! It works for me 🙂