Parse an XML value with a colon in SQL Server

  • duhast2012

    SSC Journeyman

    Points: 90

    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: 182343

    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 Journeyman

    Points: 90

    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 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply