February 7, 2017 at 1:14 pm
Hello, I have an XML document formed like the attachment. I have it in a table which ddl is:
CREATE TABLE [dbo].[tablename](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XMLData] [xml] NULL,
[LoadDate] [datetime] NULL CONSTRAINT [DF_tablename_LoadDate] DEFAULT (getdate()),
[SourceFilePath] [varchar](700) NULL
)
I am getting all NULLS when I try to query the data like follows:declare
@xml xml
,@hDoc int
,@sql nvarchar(max)
set @xml = (select s.XMLData from dbo.tablename s)
--print @xml
exec sp_xml_preparedocument @hDoc OUTPUT, @xml
select
agencyname
,agencycode
,firstname
,lastname
,dob
,ssn
,[ar]
,[az]
,[co]
,[in]
,[ky]
,[mt]
,[nm]
,[nv]
,[oh]
,[ok]
,[tn]
,[tx]
from
openxml(@hDoc, 'Agencies')
with
(
[AgencyName] varchar(1000) '@AgencyName'
,[AgencyCode] varchar(50) '@AgencyCode'
,[FirstName] varchar(100) '@FirstName'
,[LastName] varchar(100) '@LastName'
,[DOB] varchar(100) '@DOB'
,[SSN] varchar(100) '@SSN'
,[AR] varchar(100) '@AR'
,[AZ] varchar(100) '@AZ'
,[CO] varchar(100) '@CO'
,[IN] varchar(100) '@IN'
,[KY] varchar(100) '@KY'
,[MT] varchar(100) '@MT'
,[NM] varchar(100) '@NM'
,[NV] varchar(100) '@NV'
,[OH] varchar(100) '@OH'
,[OK] varchar(100) '@OK'
,[TN] varchar(100) '@TN'
,[TX] varchar(100) '@TX'
)
exec sp_xml_removedocument @hDoc
it is coming back all NULLS. i'm not sure i understand the xml file well enough to be sure i am navigating to the node(s) correctly. I cannot control nor change the xml file; it comes from a vendor that at the moment we will not make changes to.
February 7, 2017 at 5:07 pm
Either I'm not yet used to the new forum software or you forgot to attach the attachment you're talking about.
--Jeff Moden
February 7, 2017 at 8:24 pm
GRRR (at myself) ... I'm very thankful to you for looking at my problem, can't believe I forgot that attachment! Sorry. Here it is.
Thx
Isaac
February 7, 2017 at 10:09 pm
You have element-centric XML, you have to use parameter FLAG=2 in OPENXML.
Apart from that - you must use names of elements, not variables.
Variables here can be found only for Agency ID and StateLicense ID:select
[AgencyID]
,agencyname
,agencycode
,firstname
,lastname
,dob
,ssn
,[StateLicenseID]
,[ar]
,[az]
,[co]
,[in]
,[ky]
,[mt]
,[nm]
,[nv]
,[oh]
,[ok]
,[tn]
,[tx]
from
openxml(@hDoc, 'Agencies/Agency',2)
with
(
[AgencyID] varchar(1000) '@id'
,[AgencyName] varchar(1000) 'AgencyName'
,[AgencyCode] varchar(50) 'AgencyCode'
,[FirstName] varchar(100) 'FirstName'
,[LastName] varchar(100) 'LastName'
,[DOB] varchar(100) 'DOB'
,[SSN] varchar(100) 'SSN'
,[StateLicenseID] VARCHAR(100) 'StateLicense/@id'
,[AR] varchar(100) 'AR'
,[AZ] varchar(100) 'AZ'
,[CO] varchar(100) 'CO'
,[IN] varchar(100) 'IN'
,[KY] varchar(100) 'KY'
,[MT] varchar(100) 'MT'
,[NM] varchar(100) 'NM'
,[NV] varchar(100) 'NV'
,[OH] varchar(100) 'StateLicense/LicenseNumber/OH'
,[OK] varchar(100) 'OK'
,[TN] varchar(100) 'TN'
,[TX] varchar(100) 'TX'
)
It's not the final script, but you must know how to finalise it.
_____________
Code for TallyGenerator
February 7, 2017 at 10:38 pm
pisorsisaac - Tuesday, February 7, 2017 8:24 PMGRRR (at myself) ... I'm very thankful to you for looking at my problem, can't believe I forgot that attachment! Sorry. Here it is.Thx
Isaac
Do you folks have any idea that you could be sued into oblivion if all that clear text PII ever got out or that you could be required to pay identity protection fees for every person where that information got out for 2 years or more? Also, poo flows down hill on such revealed indiscretions and I'm thinking that you're sitting at the bottom of the hill on this one. You need to advise management that they need to fix this. Most of this data should be encrypted but especially the SSNs even though the SSA doesn't require it, according to their website.
--Jeff Moden
February 8, 2017 at 7:02 am
I'd recommend against using the preparedocument method of shredding XML, this is the old SQL Server 2000 way of doing it and is memory and resource intensive. You can use the .Nodes() method of the XML datatype itself like this:
SELECT list.rows.value('@id', 'integer') AS id,
list.rows.query('AgencyName').value('.','varchar(1000)') AS AgencyName,
list.rows.query('AgencyCode').value('.','varchar(50)') AS AgencyCode,
list.rows.query('FirstName').value('.','varchar(100)') AS FirstName,
list.rows.query('LastName').value('.','varchar(100)') AS LastName,
list.rows.query('DOB').value('.','varchar(100)') AS DOB,
list.rows.query('SSN').value('.','varchar(100)') AS SSN,
list.rows.query('StateLicense/LicenseNumber/AR').value('.','varchar(100)') AS AR,
list.rows.query('StateLicense/LicenseNumber/AZ').value('.','varchar(100)') AS AZ,
list.rows.query('StateLicense/LicenseNumber/CO').value('.','varchar(100)') AS CO,
list.rows.query('StateLicense/LicenseNumber/IN').value('.','varchar(100)') AS [IN],
list.rows.query('StateLicense/LicenseNumber/KY').value('.','varchar(100)') AS KY,
list.rows.query('StateLicense/LicenseNumber/MT').value('.','varchar(100)') AS MT,
list.rows.query('StateLicense/LicenseNumber/NM').value('.','varchar(100)') AS NM,
list.rows.query('StateLicense/LicenseNumber/NV').value('.','varchar(100)') AS NV,
list.rows.query('StateLicense/LicenseNumber/OH').value('.','varchar(100)') AS OH,
list.rows.query('StateLicense/LicenseNumber/OK').value('.','varchar(100)') AS OK,
list.rows.query('StateLicense/LicenseNumber/TN').value('.','varchar(100)') AS TN,
list.rows.query('StateLicense/LicenseNumber/TX').value('.','varchar(100)') AS TX
FROM (SELECT XMLdata FROM dbo.tablename) AS T(x)
CROSS APPLY x.nodes('/Agencies/Agency') AS list(rows);
edit: I forgot the StateLicense/ part for AR
February 12, 2017 at 3:01 pm
Thanks much to those who helped me, I appreciate it.
To whoever it was that apparently didn't look at the text file I uploaded before posting the rant about personal information, you may want to look at it prior to commenting again.
And of course, you have no clue who or where or when this data actually IS being encrypted, so ....
I would say it's hard to imagine being this impulsive posting such an unfounded nastygram, but , I can't say I've never ever done it.
Thanks again to all who helped.
February 14, 2017 at 10:20 pm
pisorsisaac - Sunday, February 12, 2017 3:01 PMThanks much to those who helped me, I appreciate it.To whoever it was that apparently didn't look at the text file I uploaded before posting the rant about personal information, you may want to look at it prior to commenting again.
And of course, you have no clue who or where or when this data actually IS being encrypted, so ....
I would say it's hard to imagine being this impulsive posting such an unfounded nastygram, but , I can't say I've never ever done it.Thanks again to all who helped.
I can't find any "nastygram" in this topic.
Just a friendly concern about the way personal information is handled in your company, and your position at the moment when s..t hits the fan.
You can tell all the cool stories about looking away from the data (yeah, right), but the only thing which would matter when it's time for finger pointing is that you You can tell all the cool stories about looking away from the data (yeah, right), but the only thing which would matter when it's time for finger pointing is that you had access to the data in the form of open text., so you, so you could possibly read it[/b] it.
It will make you a guilty one, unless you find anybody else sitting lower than you on this hill and being able to access the data as well.
So, instead of exposing your own nastyness you better take the advice.
And thank the adviser.
_____________
Code for TallyGenerator
February 15, 2017 at 9:49 am
"I can't find any "nastygram" in this topic"
...I respect your opinion on that, and I'm aware there are people of all languages on this board (native english speakers and non), so I can understand.
But just for your knowledge, yes, his post was what we might call a "rant", and was extremely assertive/aggressive/harsh considering that he has absolutely zero knowledge of when we do or don't encrypt this, and has no knowledge of whether or not we are encrypting that data at the appropriate places. Taken in proper context of the lack of that knowledge, it was a very impulsive thing to post.
I have worked for years (prior to now) in other industries, such as the HEALTHCARE DATA industry, and believe me, the strictness surrounding healthcare data is 100 times as strict compared to what insurance industry people call "PII". Trust me, I'm familiar with the extreme end of that strictness - much more, even, than those focusing on their 'PII".
In fact, prior jobs were banking and healthcare. Believe me, they're both stricter than insurance. I know all about HIPAA, and it's a lot more stringent than insurance PII. So yeah - I know what I'm talking about, and his rant without knowing when we actually do or don't encrypt data and where, was a poor and impulsive choice. Some people are more impulsive than others on boards. Suit yourself, but going around ranting at people with enough justification may not be a good thing to do, just my opinion.
February 15, 2017 at 10:22 am
Chris Harshman - Wednesday, February 8, 2017 7:02 AMI'd recommend against using the preparedocument method of shredding XML, this is the old SQL Server 2000 way of doing it and is memory and resource intensive. You can use the .Nodes() method of the XML datatype itself like this:
SELECT list.rows.value('@id', 'integer') AS id,
list.rows.query('AgencyName').value('.','varchar(1000)') AS AgencyName,
list.rows.query('AgencyCode').value('.','varchar(50)') AS AgencyCode,
list.rows.query('FirstName').value('.','varchar(100)') AS FirstName,
list.rows.query('LastName').value('.','varchar(100)') AS LastName,
list.rows.query('DOB').value('.','varchar(100)') AS DOB,
list.rows.query('SSN').value('.','varchar(100)') AS SSN,
list.rows.query('StateLicense/LicenseNumber/AR').value('.','varchar(100)') AS AR,
list.rows.query('StateLicense/LicenseNumber/AZ').value('.','varchar(100)') AS AZ,
list.rows.query('StateLicense/LicenseNumber/CO').value('.','varchar(100)') AS CO,
list.rows.query('StateLicense/LicenseNumber/IN').value('.','varchar(100)') AS [IN],
list.rows.query('StateLicense/LicenseNumber/KY').value('.','varchar(100)') AS KY,
list.rows.query('StateLicense/LicenseNumber/MT').value('.','varchar(100)') AS MT,
list.rows.query('StateLicense/LicenseNumber/NM').value('.','varchar(100)') AS NM,
list.rows.query('StateLicense/LicenseNumber/NV').value('.','varchar(100)') AS NV,
list.rows.query('StateLicense/LicenseNumber/OH').value('.','varchar(100)') AS OH,
list.rows.query('StateLicense/LicenseNumber/OK').value('.','varchar(100)') AS OK,
list.rows.query('StateLicense/LicenseNumber/TN').value('.','varchar(100)') AS TN,
list.rows.query('StateLicense/LicenseNumber/TX').value('.','varchar(100)') AS TX
FROM (SELECT XMLdata FROM dbo.tablename) AS T(x)
CROSS APPLY x.nodes('/Agencies/Agency') AS list(rows);
edit: I forgot the StateLicense/ part for AR
Chris,
Thanks for this, I'll have to see how I can do this on another project I am currently not working on but need to get back to soon. Using openxml has proven to work well, but your comments about resources make me question the performance and scalability of the solution.
February 15, 2017 at 10:07 pm
pisorsisaac - Sunday, February 12, 2017 3:01 PMThanks much to those who helped me, I appreciate it.To whoever it was that apparently didn't look at the text file I uploaded before posting the rant about personal information, you may want to look at it prior to commenting again.
And of course, you have no clue who or where or when this data actually IS being encrypted, so ....
I would say it's hard to imagine being this impulsive posting such an unfounded nastygram, but , I can't say I've never ever done it.Thanks again to all who helped.
It was me and I was trying to warn you. It doesn't matter where the data comes from if it's your company that get's hacked. And you just did say that you have no control over the data coming from the vendor. Try not to bite the head of people trying to help you save your own hinny.
--Jeff Moden
February 16, 2017 at 11:52 am
I'm curious that if SSN's ideally never make it onto computers, then why are they considered such sensitive pieces of data in the first place?
February 16, 2017 at 4:17 pm
patrickmcginnis59 10839 - Thursday, February 16, 2017 11:52 AMI'm curious that if SSN's ideally never make it onto computers, then why are they considered such sensitive pieces of data in the first place?
The trouble is that they DO make it onto computers.... a LOT of computers. The health-care industry is insane with them as are many other industries. I did some consulting work for a medium size company that specialized in automotive loans. They used SSN in clear text as the bloody primary key for the customers.
--Jeff Moden
February 16, 2017 at 4:45 pm
patrickmcginnis59 10839 - Thursday, February 16, 2017 11:52 AMI'm curious that if SSN's ideally never make it onto computers, then why are they considered such sensitive pieces of data in the first place?
If anyone at all can read the data, it ends up on computers. There are plenty of breaches reported with scenarios similar to someone querying the data, save it to a spreadsheet on their laptop, leave work - with that laptop and somewhere along the way, the laptop is stolen.
Sue
February 16, 2017 at 5:46 pm
I'm not sure I can understand what's such a big deal with SSN, why it should be so secret.
We here in NZ also have some ID numbers.
Driver license is used everywhere to prove my identity, my NHI (health index) number is printed on every medical form, and on wrist bands it wear when in a hospital, IRD (tax) number is printed on every letter I receive from the government agency or send to them.
All those number are not shown only probably on my forehead.
If anyone wants to get them - too easy.
But what then?
Stealing identity?
But it must take a lot more than having a single number of mine to impersonate me.
Don't Americant protect the wrong thing?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply