Parse an XML value with a colon in SQL Server
-
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> - duhast2012 - Monday, April 2, 2018 8:33 AM
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 - Eirikur Eiriksson - Thursday, April 5, 2018 1:36 AMduhast2012 - Monday, April 2, 2018 8:33 AM
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 1234Thank 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