Hello Everyone,
I have an XML document from which i would like to read a value that is placed in something called #text (file attached).
I was able to retrevie values from @name from row 20 ("TestUser") using following below query but i don't know how to control access to all other rows. For example what query should i use to retrive values from row 21 ("Remo_test User")
SELECT
MY_XML.Usery.value('.','nvarchar(max)') as 'User'
FROM (SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'C:\Users\MyUser\Documents\Lukasz\Apps\Ramo\SIN\SecutrityUsers.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('SecurityData/Users/User/Property') AS MY_XML (Usery)
WHERE
MY_XML.Usery.value('(@name)[1]','nvarchar(max)') like 'core.fullname'
Order By 1
You have to do some outer applies.
declare @x xml
SELECT @x = CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'C:\Users\MyUser\Documents\Lukasz\Apps\Ramo\SIN\SecutrityUsers.xml', SINGLE_BLOB) AS T(MY_XML)
SELECT B2.NameId AS Name,
B3.PropertyValue as PropertyValue
FROM @x.nodes('/SecurityData/Users') AS A(DataNode)
outer APPLY A.DataNode.nodes('User/Property') AS B(Property)
OUTER APPLY (SELECT B.Property.value('@name','nvarchar(max)')) AS B2(NameId)
OUTER APPLY (SELECT B.Property.value('.','nvarchar(max)')) AS B3(PropertyValue)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 8, 2021 at 1:40 pm
Awesome!
It works as i wanted to!
Many, many thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply